Bulk Insert of CSV file

Question

Post reply

Bulk Insert of CSV file

harmand

Grasshopper

Points: 10
More actions
February 24, 2011 at 6:21 am

#247257

I'm trying to use a bulk insert to load a table. Not working for me.
USE hr
BULK INSERT newhires
FROM 'c:\temp\1102.dat'
WITH (FIELDTERMINATOR = ',',
ROWTERMINATOR ='')
The problem is some of the fields contain a comma as part of the data.
..."Thorton, James",1582 Duxford,Azusa,CA,92811,02/15/1978...
Even with the double quotes around the name field it still loads as 2 fields.
How can I load the flat file with this type of data?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

You'll need to supply a format file. This post may help:

http://www.sqlservercentral.com/Forums/FindPost99982.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

harmand Grasshopper Points: 10 More actions · Answer 2

THANK YOU

Another problem. I have catalog table with a description field. If the description has a comma in it the field in the flat file is in double quotes.

There is no way to know which record will have the qoutes.

Any suggestions?

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 3

harmand (2/25/2011)
THANK YOU
Another problem. I have catalog table with a description field. If the description has a comma in it the field in the flat file is in double quotes.
There is no way to know which record will have the qoutes.
Any suggestions?

Not trying to be a smart guy here. If I'm going to get the data more than once, I get the vendor to create well formed data instead it having such anomolies in it.

--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)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

harmand (2/25/2011)
THANK YOU
Another problem. I have catalog table with a description field. If the description has a comma in it the field in the flat file is in double quotes.
There is no way to know which record will have the qoutes.
Any suggestions?

From BOL: http://msdn.microsoft.com/en-us/library/ms188609.aspx

To be usable as a data file for bulk import, a CSV file must comply with the following restrictions:
- Data fields never contain the field terminator.
- Either none or all of the values in a data field are enclosed in quotation marks ("").

You will either need to contact your data supplier and tell them to comply with these restrictions or you'll need to pre-process the file to get it into a suitable format.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato