Bulk Insert - Format File field terminator question 2

[DavidKnight]

Hi:

How do I show the terminator character in the format file for the quotation mark around the name field? The bulk insert splits the name on the imbedded comma, which makes sense! Thanks for your help! David

DK4.csv file:
"Knight,David M",1234567,101123456
"Knight,David M",1234567,101234567
"Knight,David M",1234567,101345678

DK4.fmt file:
8.0
3
1 SQLCHAR 0 40 "," 1 NAME SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 12 "," 2 EMPLID SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 20 "/r/n" 3 ACCOUNT SQL_Latin1_General_CP1_CI_AS


SQL Table:
CREATE TABLE [dbo].[DK4] (
[NAME] [varchar] (125) SQL_Latin1_General_CP1_CI_AS NULL,
[EMPLID] [varchar] (125) SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT] [varchar] (125) SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

SQL Command:
BULK INSERT DK4
FROM 'C:\Work\Payroll_Test\DK4.csv'
WITH (formatfile='c:\work\payroll_test\DK4.fmt');

 

[PDreyer]

Change the format file to

8.0
4
1       SQLCHAR       0       20      "\""                      0     junk        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       20      "\","                     1     NAME        SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       12      ","                       2     EMPLID      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       12      "\r\n"                    3     ACCOUNT     SQL_Latin1_General_CP1_CI_AS

 

[DavidKnight]

Thanks PDreyer for your suggestion!!

It worked!

David

 

[djj55]

PDreyer,
Thanks! I did not know you could use a "blank" value line in the format file. I had resorted to creating a single column table to hold the string to be output.

Have a star! This gets book marked.

djj