SQL Server – Field Terminator Problem in Format Files

bulk-insertformatimportsql server

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'  
WITH   
  ( 
     FIELDTERMINATOR ='|',
     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!

Jason

Best Answer

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.

Related Question