Import CSV with text qualifier

Question

Import CSV with text qualifier

Casper101

Hall of Fame

Points: 3892
More actions
June 20, 2011 at 12:58 am

#148128

I have a comma separated CSV file with double quotes ( " ) as my text qualifier.
I need to create a script to import this file into a table.
BULK INSERT does not work because it does not have a text qualifier indicator.
What are my options? How can I import this file using a script (it has to be scripted....) This has to be automated on a SQL express machine - there is not VS installation so i can not create a SSIS package.

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

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

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 1

Not sure you need VS installed to create an SSIS package. The SSIS Import / Export wizard could create the package for you. You would, however, need to have SSIS installed (and the service running) in order to run the package at need.

Have you looked at the BCP Utility? I don't use it, so I don't know if that will work for you or not.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

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

BCP and BULK INSERT are virtually identical now-a-days. Neither will handle true CSV (with text qualifiers, etc) without a bit of work. If the text qualifiers are used consistently from row-to-row including the header of the file, it is possible to make a BCP Format File that will suss the problem.

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

widlund_martin Newbie Points: 7 More actions · Answer 3

What worked for me was changing

ROWTERMINATOR = '\n'

to

ROWTERMINATOR = '0x0a'

in the BULK INSERT's WITH-statement .