BULK INSERT fails because of newline ROWTERMINATOR

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

BULK INSERT fails because of newline ROWTERMINATOR

Author

Topic

stringchopper
Starting Member

3 Posts

Posted - 2015-02-05 : 07:49:57

Hi,

I need to import a CSV file to a table and the CSV has an Address field that has a carriage return in it.

Example:
123 Main St.
Anywhere, CO, 99999

I'm working in Windows with SQL Server 2008. What can I do to the CSV file or from within SQL Managment Studio to get the BULK INSERT to work?

Here's my query:

BULK INSERT Contact
	From 'C:\Users\Brian\Downloads\Import-FilteredContact9c.csv'
	WITH
	(
		FIELDTERMINATOR = '|',
		ROWTERMINATOR = '\n'
	)

TIA!

Regards, stringchopper

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-05 : 08:43:50

You need to fix the data at the source. There is no way to distinguish a newline within a field from a newline at the end of a record. OTOH with a good text editor, you could do it with a regular expression. However, I presume you don't want to manipulate the data by hand. You could fix it using a script transformation in SSIS, however.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources