BULK INSERT syntax for csv file with double quoted fields?

[UltraSmooth]

I'm trying to import a large csv file into a table.
Every field in the csv file is surrounded by double quotes and fields are separated by the ',' character.

When I execute the command

BULK INSERT temptable FROM 'D:\CSVData\importfile.csv' WITH (MAXERRORS=0,FIELDTERMINATOR = '","',ROWTERMINATOR = '\n');

I get the following error:
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 1 (ADJUNIT).
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

The first column is of type 'int' so I guess the command is parsing the first field as text.

What is the proper syntax for this command to handle the double quotes around the fields?

Thanks for any help.

 

[yksvaan]

You could use normal text source and sql oledb table as destination in dts package, for the text source you can define text qualifier there. In the Transform data task's properties, check Use fast load, then what dts actually does is bulk insert, you shouldn't need command line because of performance issues. One word of advice, do not try to import/export text files over network (shares), but copy the file first on the dts server. More information in sql docs (bol, books online).

Cheers

 

[UltraSmooth]

I tried creating a DTS pacakge for this operation and it did work without any problems but unfortunately I need to run this job on command line. Thanks for you tip though.

 

[gradley]

Have you tried using..

SET QUOTED_IDENTIFIER ON

Prior to performing the BULK INSERT?

I don't have a quoted file to test this with but based on BOL, it sounds like it may do the trick.

 

[yksvaan]

You might know this, but you can run dts packages from command shell using command dtsrun. If you have package as local package, and you start this process at the server (scheduled or console session) with integrated security

dtsrun /Syour_server_name /Nyour_package_name /E

dtsrun /? shows you the other options, if you for instance have the package as structured file

Cheers

 

[UltraSmooth]

I tried SET QUOTED_IDENTIFIER ON but it did not make a difference. I realize you can run DTS pacakges from the command line but this is not what I need either. I'm stumped. Apparently you can use format files and I've found a website with a tutorial but I haven't figured it all out yet. Anyone know how to create a format file for my CSV data file to use in the BULK INSERT statement?

 

[yksvaan]

Real command line utility would be bcp, of course you are trying to start it from sql.

Actually , you don't want to use dts at all this time ?

Check out FAQ
T-SQL Hints and Tips
How to use the BULK INSERT with a file that has double quotes
faq183-1602
on forum Microsoft SQL Server: Programming > FAQs

Cheers

 

[UltraSmooth]

Thanks yksvaan, that FAQ was great, I didnt' even think to check that section. Thanks for the help!

 

[yksvaan]

Not trying to sound sarcastic or anything, but, and this is true, I googled for:
bulk double-quote formatfile
to see if there was information somewhere else in the web, but that search shows me that tek-tips faq as the first hit (small world)!

Cheers