Menu
I have a CSV file - It has many values having comma as a part of value.The commas within the fields will mislead my SSIS package to understand that file row has more columns than previously said!
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;Actually not all.Amount Column and Address Column have extra commas.
223
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.
Re: Handling 'comma' in the data while writing to a CSV. So for data fields that contain a comma, you should just be able to wrap them in a double quote. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.
The best, quickest and easiest way to resolve the comma in data issue is to use Excel to save a comma separated file after having set Windows' list separator setting to something other than a comma (such as a pipe). This will then generate a pipe (or whatever) separated file for you that you can then import.
A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
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.
91
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.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
