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

Answer 2

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.

Answer 3

Yup, K Richard is right: FIELDTERMINATOR = '","'

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

Answer 4

Try OpenRowSet. This can be used to import Excel stuff. Excel can open CSV files, so you only need to figure out the correct [ConnectionString][2].

[2]: Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

Answer 5

You could also use DTS or SSIS.

Answer 6

You have to watch out with BCP/BULK INSERT because neither BSP or Bulk Insert handle this well if the quoting is not consistent, even with format files (even XML format files don't offer the option) and dummy ["] characters at the beginning and end and using [","] as the separator. Technically CSV files do not need to have ["] characters if there are no embedded [,] characters

It is for this reason that comma-delimited files are sometimes referred to as comedy-limited files.

OpenRowSet will require Excel on the server and could be problematic in 64-bit environments - I know it's problematic using Excel in Jet in 64-bit.

SSIS is really your best bet if the file is likely to vary from your expectations in the future.

Answer 7

Do you have control over the input format? | (pipes), and \t usually make for better field terminators.

Answer 8

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.

Answer 9

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.

Answer 10

Hi,
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","[email protected]"

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

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

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

Answer 11

Another hack which I sometimes use, is to open the CSV in Excel, then write your sql statement into a cell at the end of each row. For example:

=concatenate("insert into myTable (columnA,columnB) values ('",a1,"','",b1,"'")")

A fill-down can populate this into every row for you. Then just copy and paste the output into a new query window.

It's old-school, but if you only need to do imports once in a while it saves you messing around with reading all the obscure documentation on the 'proper' way to do it.

Answer 12

Id say use FileHelpers its an open source library

Answer 13

What about when the CSV file is like this: ",1,1,Dufur2001,,ETS,,"

How bulk can ignore the " symbol

Any ideas.