ssis - How to preserve data comma ',' while exporting data from SQL Server to flat file (CSV) where column delim

admin管理员组

文章数量:1205147

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

ID	FruitsGroup
1	Apple,Mango and Orange
2	Apple,Mango, Orange, Kiwi

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

ID	FruitsGroup
1	Apple,Mango and Orange
2	Apple,Mango, Orange, Kiwi

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

ID	FruitsGroup
1	Apple
Mango and Orange	2
Apple	Mango
Orange	Kiwi

• sql-server
• ssis
• migration

Share Improve this question Follow edited Jan 20 at 15:54 marc_s 754k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 20 at 12:49 Abdul BasitAbdul Basit 1322 bronze badges 24

• 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

 |  Show 19 more comments

1 Answer 1

Sorted by: Reset to default 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"

本文标签：