Force newline terminator on BULK INSERT 1

[Glasgow]

I am trying to import data from a text file whose rows are delimited by a newline character (with no preceding carriage return). Specifying ROWTERMINATOR = '\n' to BULK INSERT doesn't work unless I have a carriage return preceding each newline. BOL states (under BCP):

A common row terminator used when exporting SQL Server data to ASCII data files is \r\n (carriage return, newline). Using both characters as the row terminator ensures that each row of data appears on its own line in the data file. However, it is only necessary to enter the characters \r\n as the terminator when manually editing the terminator column of a bcp format file. When you use bcp interactively and specify \n (newline) as the row terminator, bcp prefixes the \r (carriage return) character automatically.

I'm assuming that it must apply a similar principle with BULK INSERT - i.e. \n implies \r\n.

How do I force it to look for \n only?

 

[manmaria]

Is this help?

bulk insert dbo.blah1
from 'C:\Files\blah1_data.txt'
WITH (FIELDTERMINATOR='|')

 

[Glasgow]

Thanks but I don't think that helps me. Specifying no ROWTERMINATOR just assumes '\n' which exhibits the same symptoms.

 

[SQLSister]

Here is the code we ended up using to fix a simliar problem. It's dymanic because the actual proc this came from changes server location depending on which server is it is run on.

declare @sql varchar(1000), @path varchar(255)
 	set @path = '\\myserver\mypath\'
	--
	set @sql = 'BULK INSERT dbo.mytable
		FROM ' + char(39)+ @Path + 'myfile.txt' + char(39)
		+ ' WITH (
		  FIELDTERMINATOR = ''|'',
		  ROWTERMINATOR = ' + char(39) +  char(10) + char(39)+')'
	exec(@sql)

"NOTHING is more important in a database than integrity." ESquared

 

[Glasgow]

Why didn't I think of building then executing a string!?

That works. Thanks a million.

I assume you just use CHAR(39) to avoid rather illegible sequences of repeated single quotes.

 

[SQLSister]

I just know it worked when we did char(39) and not when we did singlequote. SAme thing with the char (10). Took us the longest time to figure this out and get this to work. Funny thing is I was just running this code when you put up the question.

"NOTHING is more important in a database than integrity." ESquared

 

[Glasgow]

Spooky! My code, for what it's worth, was as follows:

DECLARE @SqlTxt varchar(1000)
SET @SqlTxt ='BULK INSERT  LogStats FROM ''c:\web\logs\Logs2006.log''
              WITH (FIELDTERMINATOR = '' '', ROWTERMINATOR = ''' + CHAR(10) + ''')'
EXEC (@SqlTxt)

So I got away without any CHAR(39) - though it's not particularly easy to read! Thanks again.

 

[ESquared]

Any experienced SQL developer should be able to read that simple level of nested quotes. Even two levels deep shouldn't be too bad of a problem for someone with experience. Three levels starts to become a serious problem, though... but it's always easy to work backwards, one level at a time.