How do I get a MS SQL bulk insert to recognize qualified values?

Question

How do I get a MS SQL bulk insert to recognize qualified values?

tlavelle

SSC Enthusiast

Points: 111
More actions
November 3, 2011 at 7:37 am

#390753

How do I get a MS SQL bulk insert to recognize qualified values?
Lest assume I have the following 2 records that I am looking to insert:
CompanyName Address Phone
TomCo, 555 Main, 773-827-5555
"Joe,llc.", 666 Main, 773-827-6666
The second record's name value is qualified with quotes because of the comma in the value. My insert thinks this value is actually 2 different values. How do I get the bull insert to recognize this qualified value as one value and not 2?
SET QUOTED_IDENTIFIER ON
BULK
INSERT transfer..WorldShipData
FROM 'C:\TEMP\UPS_CSV_EXPORT.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
FIRSTROW=2
)
THANK YOU!

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

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

I dont know that you can say "ignore field terminators if they are between quotes" as part of the bulk insert. The way I typically approach this is by choosing a delimiter which is not included in my data. pipe( | ) often does the trick.

If you cant change the way the file is produced, then you need to parse the file and programatically do that. My first thought would be to do that in powershell. If that appeals to you I can send you some code which does similar modifications which maybe you can change to suit your needs...

Really though the answer is: dont produce flat files which use charaters as delmiters which are included in the data.

Lowell SSC Guru Points: 323518 More actions · Answer 2

bulk insert allows you to use a format file, and the format file handles the quoted delimiters for you.

without the format file, you end up having to handle the delimiters seperately...that's a pain.

the format file can be xml or a text file.

Using a Format File to Bulk Import Data

from your example, if your data has a mix of missing or included delimiters, i think you'll have to cleanse the data after import...

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

NJ-DBA SSChampion Points: 13834 More actions · Answer 3

for the record, MS says (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 ("").