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


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?


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

By : dik

If you don't have a lot of time to study bcp in great detail, check out this one:

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.

By : ZXX

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

-r 0x0a

This video can help you solving your question :)
By: admin