Bulk Insert from CSV - trouble with .FMT

I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)

This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.

8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS

I have tried '","' (single quote doublequote comma) to no avail.

I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.

Does anyone know how to resolve this?

You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:

9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""

This file works for data like this one, which I guess is the format of your data too:

1","abc","234
2","def","567

Thanks

Waseem

|||Thanks Waseem - That's just what I neeed to know. |||Hi.
I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:

"2,397", Type-A, EQTY,"2,392",John Smith

What can I do to resolve the comma problem, as well as that the " is not seen as data?|||

Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:

a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.

b) The terminator for 1st field would then be ", instead of a ,

c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,

Thanks

Waseem Basheer