I'm trying to export data from a SQL Server database to flat files (destination as .CSV) for migrations. The issue that I'm facing is to preserve the comma (,) in my content text. E.g. FruitsGroup column contains (Apple, Mango and Orange) but the .CSV is creating splitting the comma separated values and messing up the columns arrangements. How can get same data in single Remarks column.

I'm expecting this data in the .csv file (see below). There is a solution with using "" while changing delimiter but I don't want to use that.

This is in my database and it must reflect in my .csv file as well

I'm trying to export data from a SQL Server database to flat files (destination as .CSV) for migrations. The issue that I'm facing is to preserve the comma (,) in my content text. E.g. FruitsGroup column contains (Apple, Mango and Orange) but the .CSV is creating splitting the comma separated values and messing up the columns arrangements. How can get same data in single Remarks column.

I'm expecting this data in the .csv file (see below). There is a solution with using "" while changing delimiter but I don't want to use that.

This is in my database and it must reflect in my .csv file as well

This is the faulty data example what I'm getting in the .csv:

• sql-server
• ssis
• migration

• 2 Which one are you using here ADF or SSIS? if you are using ADF, then csv files will have a delimiter (any given character). If you want the , to be your delimiter, then its gonna split your Remarks column into multiple columns. What exact format of output data you need? any other character like tab as column delimiter? – Rakesh Govindula Commented Jan 20 at 13:00
• 2 So, again, what does your source data look like, @AbdulBasit ? What are you actually using to extract the data from SQL Server? – Thom A Commented Jan 20 at 13:16
• 1 Please use the edit feature, @AbdulBasit . – Thom A Commented Jan 20 at 13:19
• 2 No, i didn't; it's still entirely unclear what product you are using. – Thom A Commented Jan 20 at 13:21
• 2 But, again, I strongly recommend you fix your design. It should look something like this: db<>fiddle – Thom A Commented Jan 20 at 13:45

1 Answer 1

You have 2 solutions here:

1. Change the delimiter for your file to something other than a comma (,)
2. Define a text qualifier for your file, so that your values are wrapped in said qualifier

Changing Delimiter

To change the delimiter, open your Flat File Connection manager, open the "Columns" pane, and then change the "Column Delimiter" to something that doesn't appear in your data. Don't reset your columns if you have already defined them properly. This will result in a dataset like the following (I used a Pipe (|) for the delimiter):

ID|Remarks
1|Apple,Mango and Orange
2|Apple,Mango,Orange,Kiwi

Using Text Qualifiers

To define a text qualifier, open your Flat File Connection Manager again, and then enter a character for Text qualifier on the General pane such as double quotes ("). This will then generate a file such as the following:

"ID","Remarks"
"1","Apple,Mango and Orange"
"2","Apple,Mango,Orange,Kiwi"

If you don't want specific columns to have a text qualifier, you have to set that column's TextQualified property to False in the Advanced pane. Changing ID's TextQualified property to False results in the following:

ID,"Remarks"
1,"Apple,Mango and Orange"
2,"Apple,Mango,Orange,Kiwi"