SQL Server Bulk Insert Row Terminator Issues

By:   |   Comments (7)   |   Related: More > Import and Export


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.


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.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rahul Mehta 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

Comments For This Article

Friday, June 16, 2023 - 5:58:59 AM - jpascua515 Back To Top (91305)
OMG - Thank you for this post. This was driving me crazy.

Friday, January 29, 2021 - 11:21:56 AM - DBA Cabuloso Back To Top (88129)
If you enter a line break in SSMS it also works for Windows.
I don't know if it will show, but there is a line break between the single quotes

Thursday, July 16, 2020 - 4:42:51 PM - Bill Back To Top (86147)

Thanks. You SAVED me!

Friday, January 17, 2020 - 7:21:33 PM - Sandy Doss Back To Top (83836)

Thank you for this post.  THREE years later, this is still an issue.  I've searched for DAYS trying to figure out this problem.  Was executing a BULK INSERT command in SQLExpress2008R2, with no problems.  The database was migrated to SQLExpress2017, and the command stopped working.  Couldn't get it to recognize a carriage return, OR a line feed (\r or \n).  Data originated from a Linux box, and your hex suggestion worked marvelously!

Tuesday, August 21, 2018 - 2:46:11 AM - Prabhakar Reddy Back To Top (77258)


BCP command to export data is not working in 2016 version, below is the error message, can you please help me?

TCP Provider: An existing connection was forcibly closed by the remote host.

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Client unable to establish connection.



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

Thanks! struggled with the same issue.


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

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


get free sql tips
agree to terms