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