By: Rahul Mehta | Comments (7) | Related: 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.
Here is a file from Unix. You can see it only shows LF (line feed) at the end of each line.
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.
Import File from Mac
If we do a straight BULK INSERT, we can see that no records are loaded.
If we use a row terminator of '0x0d' which is for (CR), the data loads.
Import File from Unix
If we do a straight BULK INSERT, we can see that no records are loaded.
If we use a row terminator of '0x0a' which is for (LF), the data loads.
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.
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.
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.
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.
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
- Make a generic function which identifies all of the control characters and remove them before the bulk import.
- For more about SQL Server 2016, read these SQL Server 2016 tips.
- SQL Server Integration Services Tutorial.
- SQL Server Import and Export Tips.
About the author
Rahul Mehta is a Project Architect/Lead working at Tata Consultancy Services focusing on ECM.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
