SQL Bulk import from CSV

Question

I need to import a large CSV file into an SQL server. I'm using this :

BULK  INSERT CSVTest         FROM 'c:\csvfile.txt'             WITH     (                 FIELDTERMINATOR = ',',                 ROWTERMINATOR = '\n'     ) GO 

problem is all my fields are surrounded by quotes (" ") so a row actually looks like :

"1","","2","","sometimes with comma , inside", ""  

Can I somehow bulk import them and tell SQL to use the quotes as field delimiters?

Edit: The problem with using '","' as delimiter, as in the examples suggested is that : What most examples do, is they import the data including the first " in the first column and the last " in the last, then they go ahead and strip that out. Alas my first (and last) column are datetime and will not allow a "20080902 to be imported as datetime.

From what I've been reading arround I think FORMATFILE is the way to go, but documentation (including MSDN) is terribly unhelpfull.

Answer 1

Try FIELDTERMINATOR='","'

Here is a great link to help with the first and last quote...look how he used the substring the SP

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file