Ad

SQL Bulk Import From CSV

- 1 answer

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.

Ad

Answer

I know this isn't a real solution but I use a dummy table for the import with nvarchar set for everything. Then I do an insert which strips out the " characters and does the conversions. It isn't pretty but it does the job.

Ad
source: stackoverflow.com
Ad