locked
Handling commas ( , ) in values in CSV file in ssis ( Text qualifier ) RRS feed

  • Question

  • Hi Everyone, Hope you are safe.

    I am working on some replication project and need to create a csv file in the same format as existing process ( OOzie)

    Problem : I have a table in which few values are comma separated. To handle this scenario in ssis, i used text qualifier property  but my problem is i am getting double quotes  ( " ) in all the values in output csv files. The format i am looking for is below :

    Sample Target File

    Name,Age,Id,Salary
    "zozo,Hiren",20,143445,NULL
    Mike,30,323433," "
    "Zack,Neilson",40,656565, " "

    If you see above, double quotes (") are only with values where we have comma (,) in values (like column Name ) or with blank values but not with other values ( age,id) . when i try to create the same file in ssis, i am getting " with every value. Do we have any solution where " can come only with the values where we have , in between? we need this so that we dont change the downstream processes and i am not sure how downstream processes use this file as it is taken care by another team.

    P.S : There are 40+ columns in my file so i need something dynamic

    File i created with SSIS 

    Name,Age,Id,Salary
    "zozo,Hiren","20","143445","NULL"
    "Mike","30","323433"," "
    "Zack,Neilson","40","656565", " "

    Thanks, Shaky


    Shakky

    Tuesday, August 4, 2020 7:44 PM

All replies

  • The text qualifier option puts quotes around everything or nothing.  There is no option to change that.

    That is normal for CSV files to have double quotes around text.  That should not be a problem.

    • Proposed as answer by COZYROC Wednesday, August 5, 2020 8:38 PM
    Tuesday, August 4, 2020 8:49 PM
  • Hi Shaky,

    Could you please share the example of the source data?

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Wednesday, August 5, 2020 3:22 AM
  • Hi Mona,

    Data would look like this:

    I need to create csv file from SSIS with double quotes (" ) only in values with , in between like Name,country

    Thanks


    Shakky

    Wednesday, August 5, 2020 9:05 AM
  • Hi Shakky,

    I don't think that out-of-the-box SSIS functionality will be able to produce quotes where you need them.

    *.csv files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, and line breaks are in the middle of the actual data.

    The most reliable format for data feeds is XML.

    What if you will switch to files in XML format instead of *.csv?

    Wednesday, August 5, 2020 6:46 PM
  • There is no way to do that in SSIS out of the box.  However, it is normally not a problem to have quotes around all the fields.


    Wednesday, August 5, 2020 7:00 PM
  • Hi,

    Just wondering if you may want to produce that file with a query, e.g. instead of using text qualifier just generate that in T-SQL?


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, August 5, 2020 7:14 PM
  • Hi Shakky,

    It is possible to produce in SSIS by using Derived Column transformation with a 'smart' expression looking for the existing commas.


    Wednesday, August 5, 2020 8:39 PM
  • Hi Shaky,

    We can use the following expressions in the Derived Column Transformation.

    FINDSTRING(Name,",",1) > 0 || LEN(Name) == 0 ? "''" + Name + "'' " : Name 

    FINDSTRING((DT_STR,50,1252)Age,",",1) > 0 || LEN((DT_STR,50,1252)Age) == 0 ? "''" + (DT_STR,50,1252)Age + "'' " : (DT_STR,50,1252)Age

    Notes: "''" is " ''  " .(One double quotes + two quotes + one double quotes   )

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Thursday, August 6, 2020 6:24 AM
  • Most CSV importers cannot handle quotes sometimes and not other times.  The file must be consistently quotes around text or not.

    Thursday, August 6, 2020 4:21 PM
  • Most CSV importers cannot handle quotes sometimes and not other times.  The file must be consistently quotes around text or not.

    Hi Tom,

    I concur.

    Thursday, August 6, 2020 4:23 PM
  • Hi Shaky,

    May I know if you have anything to update?

    If my response is useful to you , please remember to mark it as answer. Thank you.

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 11, 2020 8:33 AM
  • SSIS and dealing with embedded double quotes – SQLServerCentral, Is there a way for SSIS to overlook embedded double quotes in the data so when I run my package and the "for each loop" gets executed and  SSIS and dealing with embedded double quotes 1) There may be some additional spaces in there - but you could code around that, and 2) Your embedded text could conceivably contain this combination of characters too.

    SSIS and dealing with embedded double quotes and coma , SSIS and dealing with embedded double quotes and coma. mak101. Ten Centuries. Points: 1111. More actions. March 31, 2013 at 10:24 am. #270573. I posted  0. I have a field called something like TYPE & the values are variant in it. I want the SSIS to export the result to CSV & while exporting, if it finds the , in column TYPE only that line/value should be double quoted. Eg:

    Embedded double quotes data from flat file import failing to SQL , My Script task in SSIS 2012 uses C#. How do i change the language to use VB so that i can use your code? Thursday, November 19, 2015 9  When loading data using SQL Server Integration Services (SSIS) to import data from a CSV file, every single one of the columns in the CSV file has double quotes around the data. When using the Data Flow Task to import the data I have double quotes around all of the imported data.
    Tuesday, August 11, 2020 10:08 AM