I have a data file in txt format and I used to load it with Bulk Insert in the following command:

Bulk insert [table1]
FROM 'data.txt'  
     ROWTERMINATOR ='0x0a',

The code works well. However, while I was trying to use a format file with the bulk insert to load the file, I encountered some problems specifying the field terminator for the last row.

Here is the specification for the last row in the format file:

21  SQLCHAR  0  20  "0x0a"  21  misc_costs       SQL_Latin1_General_CP1_CI_AS

With "0x0a" as the terminator, I get the following error:

Bulk load data conversion error (truncation) for row 2, column 1 (var1).

With "\n", data can be loaded, but "|" is also loaded in the last column. For example, I get "0.5|" instead of "0.5".

"|\n","0x0a","|0x0a",'\r\n' returns the same error as "|"

Bulk load data conversion error (truncation) for row 2, column 1 (var1).

I'm using sql-server 2016, windows 10 64bit.

Can anyone help with this? Thanks in advance for your help!


  • Since you've asked a few questions about it now, give this page a look: sommarskog.se/bulkload.html – Erik Darling Nov 25 '16 at 18:28
  • @sp_BlitzErik, thanks for your reply! That article is very useful and provides way more information than the MSDN site. While reading the example under 4.1, I got confused with the field terminator "\"\r\n", the quote is note really complete, is it a type? – Jason Nov 25 '16 at 18:49
  • If I remember correctly, it's an escape sequence. If you keep reading there's a better explanation further down. – Erik Darling Nov 25 '16 at 18:53

After a whole day struggling with Line Feed/Hex code, I finally found that my problem was caused by a missing columns in the format files. The dataset is new to me and I thought it had 21 rows while it actually has 22 rows. That's why sql server kept loading the last "|" from the data file.

Here is a reference for others maybe interested in loading data file from UNIX system with row terminators different from Windows, there are three methods:

  1. use bulk insert and set the row terminator as "0x0a".
  2. use a format file and set the the field terminator of the last field as "\n".
  3. Use a dynamic sql with function Char(10) to insert the line feed(LF) into the command string and then exec() the command string.

Before trying these methods, use a notepad to show all control characters to confirm the control characters used by the text files.

If none of these methods work for you, you may have some problems other than the line feed/row terminator.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.