ROWTERMINATOR value for UNIX text file 1

[bilojax]

The following code works with PC-format text files but not with UNIX-format text files.

BULK INSERT gltran.dbo.pOneField
FROM 'e:\acctg\SourceFiles\dailyfeed042.txt'
WITH
(
ROWTERMINATOR = '\n'
)

I know I can convert my UNIX files to PC format with freeware (that's what we do currently), but we have dozens of these to do every day so it would speed things considerably if SQL could read UNIX files - after all, Word and Excel and WordPad and TextPad have no problem reading UNIX-format text files. So is it as simple as using a different value for ROWTERMINATOR in the WITH clause?

 

[vongrunt]

Let's go hexadecimal... how about:

ROWTERMINATOR = '0x0a'

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

your best bet is a format file, that what I have read with regards to Unix files and BULK INSERT

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bilojax]

Denis, how would I use a format file here?

 

[SQLDenis]

before you try format file what about
WITH (ROWTERMINATOR='\r') or WITH (ROWTERMINATOR=CHAR(10))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Also take a look at this (

http://groups.google.com/group/micr...erminator+unix&rnum=7&hl=en#5ddb851cfb064190)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Lemme explain:

'\r' - won't work (carriage return)
'\r\n' - won't work (DOS/Win32 delimiter)
'\n' - won't work because bcp/BULK INSERT silently prepends \r
char(10) - runtime error
'0x0a' - works

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[bilojax]

"'0x0a' - works "

Why, yes it does - hurray! Thank you, vongrunt, and here's a little star.

Now, for extra credit anyone, is there anyway to write the query so it will automatically work with either type of file? In other words, something like

ROWTERMINATOR = '0x0a' or ROWTERMINATOR = '\n'

only something that works, as the above does not.