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

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

Answer 3

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 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

Answer 5

Alternative solution by presenting process output as file

There is an other convenient way to solve this. My solution is good if you want to upload a simple CSV, for more complex cases write a format file.

Using hex code for linefeed (-r 0x0a) would be also convenient, but it did not work for me for BCP version 17.6.0001.1 on Ubuntu 18.04.

So I used a little trick instead:

Replace unix line-endings with sed, and present its output stream as a file. The relevant part of the code:

<(< iris.csv sed 's/\r*$/\r/')

As you can below see the output of sed is presented as a file:

ls <(< iris.csv sed 's/\r*$/\r/')

output:

/proc/self/fd/11

A whole example

I have the iris.csv in the working directory:

>  head -n 5 iris.csv
sepal_length,sepal_width,petal_length,petal_width,species
5.1,3.5,1.4,0.2,setosa
4.9,3.0,1.4,0.2,setosa
4.7,3.2,1.3,0.2,setosa
4.6,3.1,1.5,0.2,setosa

I can load into MS SQL using bcp like this:

query=$(cat << EOF
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='iris' and xtype='U')
    CREATE TABLE iris (
        sepal_length FLOAT NOT NULL,
        sepal_width  FLOAT NOT NULL,
        petal_length FLOAT NOT NULL,
        petal_width  FLOAT NOT NULL
    )
GO
EOF
)

sqlcmd \
    -S localhost,31433 \
    -E \
    -d "testdb" \
    -Q "$query"

bcp \
    iris in <(< iris.csv sed 's/\r*$/\r/') \
    -S localhost,31433 \
    -T \
    -d "testdb" \
    -n \
    -t ","

Notice

1. presenting process output stream as a file with this syntax is a bashism so will not work with some other shells such as dash. It will work with zsh

2. I used windows integrated authentication for AD, yes on Linux which can do this with kerberos. Thats why the -E flag for sqlcmd and the -T flag for bcp.

Answer 6

If you don't have a lot of time to study bcp in great detail, check out this one: http://msdn.microsoft.com/en-us/library/ms190759.aspx

It will give you easy example, explain what interactive prompts mean, option to save format once you are done (if you are going to do this repeatedly) etc. etc.

If your data is big and/or you have several filds you'd like, you can make a table first then do a little trial export (bcp will take a simple select as first arg) and still pick formats interactively, column by column. You can dig into saved fmt file latter if you have some extra reason for that.

Answer 7

Yes, this is maddening. My understanding is that SQL Server bcp ALWAYS inserts a \r before whatever line terminator you would expect to be used. So, if you don't use -r, you would expect it to use \n only. But it doesn't...it stupidly inserts \r so that it can use \r\n. If you specify -r \r\n then it still won't work; I suspect because it now wants \r\r\n line ends. This is all the work of some idiot coding for the Windows world trying to make life easier for beginners and ending up making things nigh on impossible for everyone else. I experienced this problem when transferring files from Sybase to SQL server and the solution was to specify -r \r\n in the bcp out from Sybase (which works exactly as you ask it to!) and -r \n (or just don't use -r) for the SQL Server bcp in.