I've had to figure this out in the past. Here's what you need to do, using your example.
"datahere","data with a comma, more","xxx"
FMT file:
7.0
3
1 SQLCHAR 0 10 "\",\"" 1 DataHereColumn
2 SQLCHAR 0 30 "\",\"" 2 DataWithCommaColumn
3 SQLCHAR 0 3 "\"\r\n" 3 xxxColumn
Using \",\" will treat the "," as the field terminator. You need to include the forward slash before the double quote mark so the format treats it as a real character.
BULK INSERT query:
BULK INSERT database.owner.targettable FROM 'drive:\path\file.txt' WITH (FIRSTROW = 2, FORMATFILE = 'drive:\path\formatfile.fmt')
The first record should look like this when you're done:
Field1: "datahere
Field2: data with a comma, more
Field3: xxx
Next, you need to remove the leading double quote from Field1 for ALL records. If you do not have double quotes in the data, just use:
UPDATE database.owner.table
SET Field1 = REPLACE(Field1, '"', '')
Let me know if this doesn't work for you!
Edited by - mmortensen on 11/18/2003 12:01:58 PM