SQL Bulk import from CSV

Asked 2008-09-18T20:17:14.370

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.

Edit Report

3 Answers

Do you need to do this programmatically, or is it a one-time shot?

Using the Enterprise Manager, right-click Import Data lets you select your delimiter.

answered 2008-09-18T20:22:32.513

u can try this code which is very sweet if you want , this will remove unwanted semicolons from your code. if for example your data is like this :
"Kelly","Reynold","kelly@reynold.com"

Bulk insert test1
from 'c:\1.txt' with ( 
    fieldterminator ='","'
    ,rowterminator='\n')

update test1<br>
set name =Substring (name , 2,len(name))
where name like **' "% '**

update test1
set email=substring(email, 1,len(email)-1)
where email like **' %" '**

answered 2008-09-30T03:30:32.903

If you figure out how to get the file parsed into a DataTable, I'd suggest the SqlBulkInsert class for inserting it into SQL Server.

answered 2008-09-19T06:04:43.957

SQL Bulk import from CSV

3 Answers

Your Answer