CSV file has comma 1

[tirf]

I’m having problems importing csv files to SQL-Server.
The csv-files are comma-delimited.

and in one of the rows there is a comma in the data
like

Col1 col2 col3
aaa bbb ccc
ddd eee fff
gg,g hhh iii

the third row has a problem importing the data because it has a comma withn the data; this makes the data after the comma to load to the next column

is there any solution for this so the data is loaded appropriately

Thanks

 

[gmmastros]

Do you have a text qualifier in your csv file? Specifically, if there can be commas in the data, there is usually a quote qualifier, so your data would look like this:

[tt]
"aaa","bbb","ccc"
"ddd","eee","fff"
"gg,g","hhh","iii"
[/tt]

If there is no text qualifier, then there really isn't anything you can do to fix this problem unless you manually go in to the data and fix it. Personally, I would go back to the source of the data and demand that it get fixed there.

If you are going to use text qualifiers, then you need to be consistent with it. For example, the following data wouldn't work either.

[tt]
aaa,bbb,ccc
ddd,eee,fff
"gg,g",hhh,iii
[/tt]

But this would:


[tt]
"aaa",bbb,ccc
"ddd",eee,fff
"gg,g",hhh,iii
[/tt]

If there is going to be a text qualifier on a column, then the text qualifier must exist for every row in that column of the data file.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

If the original text also has a space after the comma like this:

aaa, bbb, ccc
ddd, eee, fff
gg,g, hhh, iii

Then you could import using the space as the delimiter and then remove the commas using an Update command.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[tirf]

perfect it now works

Thank you George