How to bulk insert from CSV when some fields have new line character?
Posted
by z-boss
on Stack Overflow
See other posts from Stack Overflow
or by z-boss
Published on 2010-03-25T03:35:26Z
Indexed on
2010/03/25
3:43 UTC
Read the original article
Hit count: 436
I have a CSV dump from another DB that looks like this (id, name, notes):
1001,John Smith,15 Main Street
1002,Jane Smith,"2010 Rockliffe Dr.
Pleasantville, IL
USA"
1003,Bill Karr,2820 West Ave.
The last field may contain carriage returns and commas, in which case it is surrounded by double quotes.
I use this code to import CSV into my table:
BULK INSERT CSVTest
FROM 'c:\csvfile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
SQL Server 2005 bulk insert cannot figure out that carriage returns inside quotes are not row terminators.
How to overcome?
© Stack Overflow or respective owner