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 file has been correctly validated and I have successfully imported some test data.

However I have a problem when encountering comma's in the middle of quoted text, such as the following:

"SB","7Y4386429Y0351805","B5503","","Chris",1,10,"Paypal","Hi sorry for the delay in payment, I will be sending payment soon."

(example data)

I've set the format file to correctly identify fields which are quoted and non-quoted, but the last field has a comma in the middle, and this is creating an error whilst importing.

I have the FieldTerminator set to "," and have tried leaving it out completely too, but shouldn't the terminator be ignored and instead the ones in the format file used?

I don't understand why the comma would cause the field to end, as the field is surrounded by quotation marks. Is this by design? Surely the comma should be ignored as it doesnt match any of the field terminators in the format file?

What are my options here? The data in question is coming from Paypal so there's not much chance of them changing the way it's exported.

Any ideas?

If it matters, I have shown the first part of my format file below (the real file contains 56 rows). All 56 rows have the same SQLCHAR datatype, the same length and the same end value.

10.0                            
56
1   SQLCHAR 0   0   "\""    0   DUMMY   SQL_Latin1_General_CP1_CI_AS                            
2   SQLCHAR 0   100 "\",\"" 1开发者_JAVA技巧   CH  SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR 0   100 "\",\"" 2   TransactionID   SQL_Latin1_General_CP1_CI_AS
4   SQLCHAR 0   100 "\",\"" 3   InvoiceID   SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR 0   100 "\",\"" 4   PayPalReferenceID   SQL_Latin1_General_CP1_CI_AS

Any help would be much appreciated.

Thanks,

Chris.

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.

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.