Can SQL Server bcp in a file with Unix line endings?

Question

I'm trying to use the SQL Server bcp utility to import a text file from a samba share. bcp is choking on the Unix line endings. I'm sure I could add an intermediate step, either on Unix or Windows, to change the line endings to Windows-style. But I would prefer to import the files from Unix without modification.

Anybody know if there's a way to tell SQL Server bcp to look for Unix line endings?

Answer 1

The simple answer is to use hex, as was mentioned in one of the comments, as the row terminator:

-r 0x0a

Answer 2

You have to use a format file with bcp and specify the terminator as \n. The interactive command line will always append \r, where a format file will use exactly what you specify. Reference http://www.eggheadcafe.com/software/aspnet/32239836/bcp-out-with-char10-as-row-terminator.aspx.

Creating a format file is explained pretty well in BOL but comment/update your original post if you need help.

Answer 3

have you tried to set the ROWTERMINATOR = '\n'?

Answer 4

I don't think you can do this from the bcp command line. But, I think the following SQL version will work.

DECLARE @Command nvarchar(1000)

SET @Command = N'BULK INSERT MyTable
FROM ''<path\file>''
WITH (ROWTERMINATOR = '''+CHAR(10)+''')'

exec sp_executeSQL @Command