I'm using SSIS and trying to import a csv file. However, only 1 field has a text qualifier - double quotes " - which in turn also contains commas. Even if I specify the " as a qualifier, SSIS seems to ignore this and still splits the data into the next field - is there a solution or do all columns need the qualifier for it to work?

This is an example of the data:

Field1,    Field2,    Field3,       Field4
ABC,        123,       "QWER,ASD",  ZXC

So when I import at the moment, SSIS is splitting Field3, even though I have specified " as a text qualifier - why is this?

I am defining the text qualifier thus:

CodePudding user response：

The problem is your file, it's malformed. You have leading spaces prior to your values ( 123) and a mismatch of columns in your rows; the header has 5 columns and the data row 4 columns. For a value to be correctly text qualified the qualifier must be at the start of the value:
" QWER,ASD" Correct
"QWER,ASD" Incorrect

You need to fix your file first. Presumably you don't want the leading spaces, so it should look like this:

Field1,Field2,Field3,Field4
ABC,123,"QWER,ASD",ZXC

Then SSIS works as you expect it to:

If you do want the leading spaces (in the values and column names) it should look like this:

Field1,    Field2,    Field3,       Field4
ABC,        123,"       QWER,ASD",  ZXC

Which also works as you would "expect":