Load CSV file which delimited by comma

Question

Hi All Expert,

I have one general question, I want to load the data from CSV file which is delimited by comma, it looks like the following:

ID, Name

"1","A"

"2","B"

"3","C,"

You can see that in the third row, there is another comma in Name field, how to overcome those Issue?

Thanks.

Answer 1

There is nothing to overcome.  You have a comma delimited file with string delimiters.  Set the field delimiter to comma and the text  qualifier to a double quotes

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 2

Yes, Thanks.

If the text looks like this one:

ID, Name

"1","A"

"2","B"

"3","C""A"

There exists additional doble quotes in the Name, How to parse that one?

Thanks.

Answer 3

It will treat the extra "" as a " inside the string.  So the value loaded from the name field would be C"A

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 4

Thanks for your good reply.

So what shall I do for that case, I mean I want to get the final record like

ID, Name

3, C""A

How to achieve that one?

Answer 5

I am not sure if this kind of records are accepted in SSIS.

If so you can use a derived column after reading the data and replace " with "" using this expression:

(DT_STR,10,1252)REPLACE(C2,"\"","\"\"")

Regards,Eshwar.

---

--Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

Answer 6

Hi,

If the file type is CSV and column value is A,C""A ("A","C""A" ) the flat file component with option column delimiter (,) and text qualifier (") would be reading as A,C""A

Thanks,

Vipin

Answer 7

When you have data that looks like that, then the best bet is to stop using CSV files and use a fixed format file instead

---

Chuck Pedretti | Magenic – North Region | magenic.com