Bulk insert and Double Quotes

Hi,

i have been struggling with this problem for a few weeks so i am really hoping that some one can help me.

i have a system to in importing large amounts of data from text files in the a SQL 2005 DB. work fine 90% of the time but there are 2 files that are causign me really problems.

the delimiter in the fil is "," and there are commas with out quotes in some fo the fields so i can not ignor the quotes.

this command will import the files fine

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","')

but as not all the fields in [Raw Call Data] are used all of the time i need to use format files.

so my question is simple who do i do this with a format file ?

Code Snippet

Bulk Insert [RawCallData] From '\\Gci-linc-srv06\Billing\SQL Filters\GCI\Test.csv'

WITH

(FIELDTERMINATOR = '","'

, FORMATFILE = 'format_file_path')

|||

that will work, but i have to use a format file as there are 50 fields in raw call data and i am only use 19 for this file, other files that are imported use all 50.

I have found the solution my self by accident

you can use the following format file to do it

9.0
19
1 SQLCHAR 0 255 "\",\"" 1 Field1 Latin1_General_CI_AS
2 SQLCHAR 0 255 "\",\"" 2 Field2 Latin1_General_CI_AS
3 SQLCHAR 0 255 "\",\"" 3 Field3 Latin1_General_CI_AS
4 SQLCHAR 0 255 "\",\"" 4 Field4 Latin1_General_CI_AS
5 SQLCHAR 0 255 "\",\"" 5 Field5 Latin1_General_CI_AS
6 SQLCHAR 0 255 "\",\"" 6 Field6 Latin1_General_CI_AS
7 SQLCHAR 0 255 "\",\"" 7 Field7 Latin1_General_CI_AS
8 SQLCHAR 0 255 "\",\"" 8 Field8 Latin1_General_CI_AS
9 SQLCHAR 0 255 "\",\"" 9 Field9 Latin1_General_CI_AS
10 SQLCHAR 0 255 "\",\"" 10 Field10 Latin1_General_CI_AS
11 SQLCHAR 0 255 "\",\"" 11 Field11 Latin1_General_CI_AS
12 SQLCHAR 0 255 "\",\"" 12 Field12 Latin1_General_CI_AS
13 SQLCHAR 0 255 "\",\"" 13 Field13 Latin1_General_CI_AS
14 SQLCHAR 0 255 "\",\"" 14 Field14 Latin1_General_CI_AS
15 SQLCHAR 0 255 "\",\"" 15 Field15 Latin1_General_CI_AS
16 SQLCHAR 0 255 "\",\"" 16 Field16 Latin1_General_CI_AS
17 SQLCHAR 0 255 "," 17 Field17 Latin1_General_CI_AS
18 SQLCHAR 0 255 "," 18 Field18 Latin1_General_CI_AS
19 SQLCHAR 0 255 "\r\n" 19 Field19 Latin1_General_CI_AS