Cannot bulk import CSV file with double quotes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cannot bulk import CSV file with double quotes

Author

Topic

surfoman
Starting Member

4 Posts

Posted - 2009-12-08 : 14:31:47

Hello,

I have a simple query (see below) to import a comma delimited CSV file into a SQL Server 2005 database table. The Bulk Insert command works fine for File 1 (see below) which does not contain any double quotes around some strings. However when I import File 2, the import does not store the fields correctly as it splits the data on the commas within the double quotes.

Please note that this is a small test to reflect a real case scenario I need to implement. I cannot change the delimiter and the string fields in the CSV file may or may not be surrounded by double quotes.

I suspect I need to adjust the field terminator but not sure how.

Thanks in advance.

--------------------------------------------------------------------

File 1: test-no-quotes.csv
--------------------------
ID,Name,Address
1,John Doe,1 Sunny Street
2,Fred Bloggs,24 High Street

File 2: test-quotes.csv
-----------------------
ID,Name,Address
1,John Doe,"1, Sunny Street"
2,"Fred, Bloggs",24 High Street

Bulk Insert SQL statement
-------------------------
BULK INSERT xImportTable
FROM 'C:\Files\CSV\test-no-quotes.csv'
WITH
( FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-12-08 : 21:54:58

Is it possible for you to create the csv file with a different delimiter and not use one of the very commonly used symbols like , or tab or " or' etc and use areally weird delimiter - a combination of symbols?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

surfoman
Starting Member

4 Posts

Posted - 2009-12-09 : 05:01:28

Hi Dinakar,

Unfortunately I have no control over setting up the CSV files. Had I done so I would have created my own delimiters.

Since my last post I have figured out that I could use a format file.

Here is another example CSV file which better illustrates what I am referring to:

---------------------------

ID,TextField1,TextField2
23,This is some text for this field,This is some other text
34,”This field, contains a comma and double quotes”,”So does this field, as you can see”
99,Somtimes it may not have double quotes,”Sometimes, it suddenly does”

---------------------------

As you can see from above, this is a perfectly acceptable CSV file but unfortunately I cannot control the consistency of the double quotes. The supplier of the data feed only appears to surround a field in double quotes if it contains the comma delimiter.

I have tried implementing a query using the MS SQL BULK INSERT query.

Here is an example:

BULK INSERT TempTable
FROM 'C:\Files\CSV\example.csv'
WITH
(
FORMATFILE = 'C:\Files\CSV\example-format.txt'
)

The problem I have here is the format file. I cannot get it to work on string fields in which some rows have the double quotes and other rows do not.

I managed to find an example on the internet here:
http://www.tek-tips.com/faqs.cfm?fid=1602

Although this works fine for fields surrounded by double quotes, as soon as you remove the double quotes from one of the fields the bulk insert fails.

So perhaps if anyone familiar with format files can help then I may have a solution.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-16 : 15:30:53

Have you tried importing with SSIS?

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources