With SSIS, the flat file connection manager messes up with csv files having "," in values in the range of thousands

Question

I had data in a CSV file, which appears in a notepad something like this:

enter image description here

In SSIS, when I try to load this file in a Delimited format, the data which appears in the preview gets messed up due to the commas which occur in the numeric values, eg. in thousands and millions. The data looks something like this:

enter image description here

Is there any way in which this problem can be taken care of in the connection manager itself ?

Thanks!

When setting up the source, did you setup text-qualifiers (ie, `"` in the above case)? — Anthony Forloney, May 11 '15 at 18:23
Sorry, I'm a newbie, workign in SSIS for the first time. Got it now though. Thank you ! :) — user2673722, May 11 '15 at 18:27
No need to apologize -- I had encountered a similiar issue my first time working with SSIS, we all have to start somewhere — Anthony Forloney, May 11 '15 at 18:28

score 2 · Accepted Answer · answered May 11 '15 at 18:24

2

Use Text Qualifier as shown here:

enter image description here

This will take care of the columns that have quotes inside. Sometimes it gets really bad with CSV data, and I've had to resort to script components doing some cleanup, but that's really rare.

answered May 11 '15 at 18:24

Raj More

47,048
33
131
198

With SSIS, the flat file connection manager messes up with csv files having "," in values in the range of thousands

1 Answers1

Linked