none
Problem with multiple commas and quatation in data when exporting to a CSV file RRS feed

  • Question

  • I have a table that I am exporting via a SQL server table to a .csv file, via the flat file connection manager within SSIS. My issue is that one of the fields in my table contains multiple "," (commas) and quatation, so when the file is created it creates new columns in the .csv file because the field is "," comma delimited on the column. Is there anyway to get round this without having to get rid of this issue?

    Example row value: Pavan, JPD, 29", 8V 10GR, 6" Feed 44x132

    Entire text (Pavan, JPD, 29", 8V 10GR, 6" Feed 44x132) is a value of one row for a column

    Thursday, June 15, 2017 10:00 PM

All replies

  • Hi PavanJ9,

    Because the column has both comma and double quotes, you could try to replace the comma first with unique special character then export to .csv file, then replace it again with comma in CSV file using replace feature. For example:

    declare @test as table (items varchar(max))
    
    insert into @test values ('Pavan, JPD, 29", 8V 10GR, 6" Feed 44x132')
    
    select REPLACE(items,',','-') as items from @test

    Best Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 16, 2017 6:20 AM
    Moderator
  • Hi Pirlo,

    Thanks for your time. I'm looking to automate this entire process instead of manual effort from the user. It would be great if you help me on that.

    Thanks,

    Pavan


    • Edited by PavanJ9 Tuesday, June 20, 2017 9:20 PM
    Tuesday, June 20, 2017 9:19 PM
  • Have you text-qualified your columns? That will allow subsequent processes (if they're developed properly) to identify what is a delimiter and what is part of the text

    Should give you something like

    Pavan, JPD, "29"", 8V 10GR, 6"" Feed 44x132"

    Tuesday, June 20, 2017 10:29 PM
  • Thanks for your response Ryan.

    Yes, I have tested by specifying text-qualifier and played with different delimiter options, still I'm getting new columns in the output csv file.

    Thanks.

    Tuesday, June 20, 2017 11:20 PM
  • How are you determining there are new columns?
    Wednesday, June 21, 2017 12:13 AM
  • I think Ryan has eluded to it, but are you replacing the double quotes with two double quotes in the source? That combined with text qualified double quotes should get you there.

    Sample CSV below...

    Column1,Column2
    "Pavan, JPD, 29"", 8V 10GR, 6"" Feed 44x132","MyTest, JPD, 5"", 8V 10GR, 6"" Feed 50x132"
    "MyTest, JPD, 5"", 8V 10GR, 6"" Feed 50x132","Pavan, JPD, 29"", 8V 10GR, 6"" Feed 44x132"

    Wednesday, June 21, 2017 3:50 AM
  • Hi PavanJ9,

    In my opinion, it's hard to use different delimiter as the sample data you shared exists multiple delimiter. (I can reproduce the issue even though using different delimiter). Just try to use the method I shared before, you could do replace task with script task connected to the Data Flow Task if you would like to accomplish the requirement automatically. 

    Please refer to : How to: Programmatically Search for and Replace Text in Documents

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 23, 2017 10:05 AM
    Moderator
  • Hi Pirlo,

    Thanks for your time. I have tried using script task in SSIS and I'm not able to get the expected result.

    Basically this solution includes 2 parts

    1. Exporting the data to csv format after replacing ",". This works fine

    2. Use script task to automate the manual replacing of "-". This doesn't works for me and I have used below code:

    string text = File.ReadAllText("D:\\abc.csv");
                text = text.Replace("-", ",");
                File.WriteAllText("D:\\abc.csv", text);
                Dts.TaskResult = (int)ScriptResults.Success;

    This script apparently creates a new csv file after replacement of "," at run time. I'm able to replace "," successfully, but at the end of the day I ended up creating csv with new columns again. I think I'm using the wrong script and I'm not able to find the correct script which replaces the character "-" with "," for a csv file.

    Your comments and suggestions are greatly appreciated.

    Thanks.


    Tuesday, June 27, 2017 1:37 AM