'Comma within fields in CSV file -import to DB using SSIS
I have a CSV file - it has many values with comma as a part of them. The commas within the fields will mislead my SSIS package making it seem like the row has more columns than previously.
How to resolve this?
Eg:
Name,Amount,Address
Me,20,000,My Home,India
you,23,300,Your Home,Where
here only 3 columns exist but SSIS assumes all commas used to separate fields. However, that is not the case: Amount column and Address column have extra commas.
Solution 1:[1]
Use Text Qualifier in Flat file Connection Manager as ". This will separate only values out of quotes by commas. Your data from db will be enclosed in quotes.
Solution 2:[2]
You will need to ensure that all values in the file that have commas as part of the text are enclosed in double quotes ("). Then you can set the text qualifier in your flat file source, and the commas will be loaded as part of the string fields.
Solution 3:[3]
I feel that a better way of doing this is by using a Derived column Transformation Editor in SSIS Data Flow, and changing the expression to surround the string with double quotes.
For instance if the column "Address" has records with commas, then create an expression such as
"\""+Address+"\""
in the Derived Column Transformation Editor.
I think this is a good option if you are not willing to transform the source.
Solution 4:[4]
In the flat file connection manager editor, change the text quantifier to double quotes (") instead of <none>.
This will take the content from one column as a single entity without splitting when there is a comma (,) in between text.
Hope this helped.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | sakshi jain |
| Solution 2 | N West |
| Solution 3 | Sykonba |
| Solution 4 | Jith |