Comma's Causing A Problem Using Bulk Insert And A Format File

I'm trying to import .CSV files using BULK INSERT on SQL Server and a Format File. I have created the format file and set the correct field terminators for each value. The format

Solution 1:

You've done it right as far as I can see - taking knowledge from here:

http://www.sqlservercentral.com/Forums/Topic18289-8-1.aspx#bm87418

Essentially, changing the separator to "\",\"" should be enough as the comma mid the last field is not "," but ,

Try setting the first and last separators as in the link ("\",\"") and ("\"\r") and see if that helps?

Or, preprocess the files and replace "," with some junk like ##$## and replace , with . (or some other character) and then ##$## with "," and then import? Unless the , is vital in the last field, a dot usually does the trick.

Solution 2:

Further to this, the FIELDTERMINATOR value doesn't seem to have any effect as the result is still the same whether I include this value in the SQL for BULK INSERT. So I will leave it out from now on as the Format File terminators take over.

The problem still exists though, and it seems very odd to me why a single comma in the middle of quoted text would cause the field to end.