Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Bulk Insert Row Terminator Issues


By:   |   Read Comments (2)   |   Related Tips: More > Import and Export

Problem

We often need to import data into SQL Server from a file. Sometimes unwanted end of line characters are part of the source file and these can create issues when importing the data. One of the ways to avoid this issue is to use SQL Server Integration Services (SSIS), but it doesn't always make sense to use SSIS for simple operations. So I use BULK INSERT and I sometimes face issues with Line Feeds and Carriage Returns. In this tip I cover some examples and how to fix the issue.

Solution

In this tip we will discuss how to use the ROWTERMINATOR to exclude control characters, so the import is successful. I have sample data with the name and location of a person in a text file and I need to import this into a table named NameLocation in my database.

As you can see below, the source file row terminators can look different depending on where the data comes from.

Here is data from a Mac.  You can see it only shows CR (carriage return) at the end of each line.

Sample Data

Here is a file from Unix. You can see it only shows LF (line feed) at the end of each line.

Sample Data

Here is a file from Windows.  You can see it shows CR and LF (carriage return and line feed) at the end of each line.

Sample Data

Import File from Mac

If we do a straight BULK INSERT, we can see that no records are loaded.

Sample Data

If we use a row terminator of '0x0d' which is for (CR), the data loads.

Sample Data

Import File from Unix

If we do a straight BULK INSERT, we can see that no records are loaded.

Sample Data

If we use a row terminator of '0x0a' which is for (LF), the data loads.

Sample Data

Tests for Windows File

I tried a bunch of other options which should work to import the file into SQL Server.

Terminate Line Feed using New Line Character n

The New Line character isn't recognized and it gives us an error as shown in the below image.

New Line Character

Terminate Line Feed using Carriage Return Character r

The Carriage Return gets recognized, but it divides the entire data into two columns which is not what is expected. So again the result is wrong.

Carriage return Character

Terminate Line Feed using Character equivalent value i.e. char(10)

If we use char(10) which is a line feed equivalent character code, it will execute but without any results being pulled.

Line Feed Character equivalent

Finally Hexadecimal Code worked

Finally, after everything I have tried, it seems the only option that works is to use the hexadecimal equivalent value of line feed character and it will pull the results as shown below.

Hexadecimal code for Line Feed Character

Note: Windows files should have a CR LF at the end of each line.  If that is the case, there is not a need to use the row terminator option, because the data should load fine.  I only used it for this example to show you what works and what does not work.

Conclusion

Try and use hexadecimal values to identify control characters in bulk insert operations for a smooth bulk insert transaction.
Next Steps


Last Update:






About the author
MSSQLTips author Rahul Mehta Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, March 17, 2017 - 5:41:25 AM - Wilfred van Dijk Back To Top

Thanks! struggled with the same issue.

 


Friday, March 17, 2017 - 3:54:52 AM - John Perry Back To Top

Yes Hex notation can be used but otherwise the correct line terminator for a new line is '\n' as opposed to just a plain 'n', similarly for carriage return it should be '\r' and not 'r'.

 


Learn more about SQL Server tools