Format file - Help using text qualifiers and delimiters

[bmacbmac]

Hi,

I am trying to import a data file with quotes as the text qualifier and a comma delimiter. Normally this wouldn't be a problem, but they have commas within fields. I am trying with a format file: Here is my setup:

create table test (col001 varchar(200), col002 varchar(200), col003 varchar(200))

I have a text file (testdata.txt) with the following data:

"blah","smith, brian","jason"
"br","elaina","bobby"
"jason","justin","brian"


I have a test3.fmt format file set up as:

10.0
3
1 SQLCHAR 0 200 "," 1 Col001 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 200 "," 2 Col002 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 200 "\"\r\n" 3 Col003 SQL_Latin1_General_CP1_CI_AS


Then I run:

bulk insert test
from 'c:\temp\testdata.txt'
with (FORMATFILE = 'C:\temp\test3.fmt')

How can I set up the correct delimiter in my format file. As it is now I end up with col001: "blah col002: "smith col003: brian","jason

I would hope for: col001: blah col002: smith, brian col003: jason

Is there a way to accomplish this? I tried "","" as my terminator but that didn't work.