Double quotations in Bulk insert - csv file

Question

Double quotations in Bulk insert - csv file

Clark Bones

Mr or Mrs. 500

Points: 551
More actions
May 4, 2009 at 4:47 am

#137226

Hello!
I have a simple csv-file that I want to import into a table. I have some code and the file is imported, but the columns in the file that contains text
are stored in the database table with a beginning quote and a final quote.
Text in file:
SampleCompany
Text in table:
"SampleCompany"
I do not want the quotes.
Fields with numbers are imported correctly.
BULK INSERT
ImportDB.CustomerDB.DetailsData
FROM
'C:\mycsv.csv'
WITH
(
FIELDTERMINATOR =';',
ROWTERMINATOR ='',
FIRSTROW=2,
CODEPAGE = 'RAW',
DATAFILETYPE = 'char'
)
TIA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

Hi. The addition of enclosing double-quotes is not the normal behavior. You say it's a simple csv file, but there's certainly something different about it. For one thing, your code shows that the fields are delimited with semi-colons rather than commas.

If you're certain the source ("mycsv.csv") doesn't already have quotes around text fields which Bulk Insert is simply not removing (open it with notepad to see), then maybe you could post a sample as an attachment to your next post so that we can see exactly what its format is. You'll probably want to desensitize the data, of course -- don't put real customer data out here.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Clark Bones (5/4/2009)
Hello!
I have a simple csv-file that I want to import into a table. I have some code and the file is imported, but the columns in the file that contains text
are stored in the database table with a beginning quote and a final quote.
Text in file:
SampleCompany
Text in table:
"SampleCompany"
I do not want the quotes.
Fields with numbers are imported correctly.
BULK INSERT
ImportDB.CustomerDB.DetailsData
FROM
'C:\mycsv.csv'
WITH
(
FIELDTERMINATOR =';',
ROWTERMINATOR ='',
FIRSTROW=2,
CODEPAGE = 'RAW',
DATAFILETYPE = 'char'
)
TIA

There's a couple of ways to handle this very common "text qualified" form of CSV. If ALL the text columns have the double-quote text qualifier, then you should probably create a BCP Format file. The delimiter for a "," would be "\",\"" (one \" for each couble-quote) and you'd have to tell it to ignore the first character as a column if the first character in the line was a double quote from a text field.

The other way is to look up how to build a Linked Server in Books Online. There you'll find a dandy example of how to make a "Text" linked server that does all that stuff auto-magically. It will, however, be substantially slower than Bulk Insert with a format file.

Another way... you could just import into a staging table using a comma delimiter and use REPLACE to get rid of the quotes.

Last but not least, you could beat the daylights out of the information provider and have them give you a nice, easy to manage TAB delimited file that takes all that pain away. If the beating doesn't work, try pork chops... Take the vendor out to dinner... feed him pork chops... at close range with a slingshot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)