mssqltips logo

Line Split Issues when Copying Data from SQL Server to Excel

By:   |   Updated: 2015-01-01   |   Comments (68)   |   Related: More > SQL Server Management Studio

Problem

Some of our developers noticed an issue a few days after celebrating our successful SQL Server 2012 upgrade. When they run a query in SQL Server Management Studio and copy and paste the results to Excel, each row gets split into many rows in Excel instead of one row like it used to do. They immediately started blaming SQL Server 2012, because no other changes were made, why is this happening?

Solution

The reason behind this issue is hidden within the data. In earlier SQL Server versions, the carriage return (\n\r) wasn't taken into consideration when copying data from SQL Server to Excel. This behaves in a different way in SQL Server 2012, as it preserves the carriage return (\n\r) and therefore splits the row into multiple rows in Excel when pasting.

Let's look at an example:

USE [MSSQLTipsDemo]
GO
 
-- Create New Table
CREATE TABLE [dbo]. [CountryInfo](
       [CountyCode] [nvarchar](20) NOT NULL
) ON [PRIMARY]
 GO
 
INSERT INTO [CountryInfo] VALUES 
(' This Record Contains the first        
Country    
JFK');
INSERT INTO [CountryInfo] VALUES 
(' This Record Contains the second        
Country    
LON');
INSERT INTO [CountryInfo] VALUES 
(' This Record contains the third        
Country    
CAI');

SELECT * FROM [MSSQLTipsDemo].[dbo].[CountryInfo]

When you run the SELECT statement in SQL 2008 R2 and then copy the results to Excel it gets copied as follows:

Run the SELECT statement in SQL 2008 R2, copying it to EXCEL

However, copying the same result set from SQL 2012 to Excel does the following:

Copying the same result set in SQL 2012 would show different results in EXCEL

Replacing the carriage return char(13) and the line feed char(10)

To avoid such issues, you need to clean the data by replacing the carriage return (char(13)) and line feed (char(10)) in your SELECT statement using the following query:

SELECT replace(replace(CountyCode, char(10), ''), char(13), '') 
FROM [MSSQLTipsDemo].[dbo].[CountryInfo]

And then our results end up as expected as shown below:

You need to clean the data by replacing the carriage return (char(13)) and line feed (char(10))
Next Steps
  • Let's trust our technology; the data is cleaner now in SQL Server 2012.
  • This leads us to the most important point – "Data Quality"; do we really need this in field x or field y? If yes we keep it as is and use the above workaround to copy our data. Otherwise, you could clean the old data and make sure that newly added data is cleaned.


Last Updated: 2015-01-01


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources




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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, September 23, 2019 - 1:46:16 PM - José Luis Back To Top

Thank you!. It worked as you describe it. Very kind of you to share.


Monday, August 12, 2019 - 4:30:39 PM - Selva Back To Top

Great tip. Saved my hours of research. 


Wednesday, May 29, 2019 - 3:55:46 AM - Kwena Back To Top

Thank you very much.


Friday, December 07, 2018 - 8:43:06 AM - Harish Back To Top

this was really helpful; thanks a lot buddy. 


Thursday, November 22, 2018 - 1:10:30 AM - SD Back To Top

Thanks so much, this worked. relieved of this issue that I have been suffereing with for so long. :)


Monday, November 12, 2018 - 5:15:51 AM - Fred Back To Top

Don't ask me why but the following workaround is also working fine and PRESERVING ntext content format.

It is all about embedding the ntext field between double-quotes.

As the add function (+) can't be used with ntext, we have to cast ntext into nvarchar anyway.

Specifying MAX as nvarchar size was necessary otherwise the result was truncated to 30 char only.

SELECT '"' + cast(CountyCode as nvarchar(MAX)) + '"'
FROM [MSSQLTipsDemo].[dbo].[CountryInfo]

1/ Execute query to get result into a grid
2/ Select all and copy with headers
3/ Open new Excel worksheet and paste data in it. Excel is interpreting double quotes as text delimiter.

N.B.: for unknown reason, this solution doesn't worked for me when saving query result into a .csv file, opening Excel, and opening csv file with csv import wizard.

Friday, October 26, 2018 - 4:00:32 PM - kiran joshi Back To Top

 

 my the issue as same as mention above. but in my office with same sql version in my coworker's machine it copied fine but only in mine it happend like that.


Wednesday, October 17, 2018 - 6:03:01 AM - @robaxm Back To Top

 Thanks, this one was very useful!👍🙂

 


Thursday, October 11, 2018 - 4:32:24 PM - Jessica A Brucks Back To Top

Just wanted to say thank you so much! I have queries I have to run several times for my users and some of the larger fields would split up. This works perfectly in SQL 2016 and saved me hours of work trying to get the data extracted when it kept splitting lines.

 


Thursday, September 27, 2018 - 11:23:29 AM - Tariq Abulaila Back To Top

Asalamo aalaykom, Ahmad. 

Usually when I am researching something for work, I just find the information I need and move on. 

This time I wanted to take a second to actually thank you, the author, for the valuable information in this post, and to say that over 3 years later (currently 09/27/2018), people like myself are still finding this information useful.  Thanks so much brother, and keep up the great work.  :)

Tariq Abulaila


Friday, April 13, 2018 - 9:56:20 AM - mike Back To Top

 hi , this one line of code  worked the best, saved my day

 

i am suprice that data export in SSMS doesnt do this  , shame on Microsoft

 

thanks for help

 

<<  cast(replace(replace(cast(MyDataField as nvarchar(max)),char(10), ''), char(13), '') as nvarchar(max)) as MyDataField

 


Wednesday, April 11, 2018 - 4:52:20 PM - shiva Back To Top

 

 

Thanks. it worked for me.


Wednesday, April 11, 2018 - 4:51:14 PM - shiva Back To Top

 

 

 

This is an absolutely fantastic answer.


Wednesday, March 28, 2018 - 7:16:34 AM - rmpbklyn Back To Top

 

ms will have to update this LOL. reverted back to 2005  for the moment (on the client). There is too much code then to re-write years of code.

At the least ms should have an opt out option, add great but have op out . 


Monday, February 26, 2018 - 4:54:53 AM - Cobus Back To Top

Hi there,

 

I was strugling with the same issue. 

My data was defined as ntext. SQL did not like the replace:

Argument data type ntext is invalid for argument 1 of replace function.

To get around this I did the following:

cast(replace(replace(cast(MyDataField as nvarchar(max)),char(10), ''), char(13), '') as nvarchar(max)) as MyDataField

It looks a bit clumbsy but it works.

 


Saturday, January 27, 2018 - 7:05:28 PM - Abdul Back To Top

Hi Ahmad,

This is very helpful.

Thanks.


Tuesday, January 23, 2018 - 3:59:54 PM - Fernando Back To Top

 Thank you, 

Amazingly the first result of google search, it worked perfect 


Monday, January 15, 2018 - 11:05:47 PM - Terence Back To Top

Hi Ahmad

Thank you so much for this! It's perfect and solved a problem I was struggeling with for hours.

 

Terence


Thursday, January 04, 2018 - 10:54:33 AM - Keith Back To Top

 Sorry, Ahmad.

 

I am grateful for your help, but this must just be "above my pay grade." I still don't understand and don't want to waste your time.

 

I did not export data into a spreadsheet, I took an existing spreadsheet and cleared the data, then modified the columns -- including merging 2 of them -- to use the design for inputing new data.

 

Keith

 


Thursday, January 04, 2018 - 1:50:15 AM - Ahmad Yaseen Back To Top

 Hello Keith,

Thank you for your input here.

Please modify the same query you used to use when exporting the data to excel by changing the name of the two columns that you faced a problem when merging them by replacing the @text with the column name in the below query, or provide me with your query and specify the two columns with problem and i will rewrite it for you:'

replace(replace(replace(@text, char(13), ''), char(10), ''), char(9), '') 

Best Regards,

Ahmad

 


Tuesday, January 02, 2018 - 7:59:47 PM - Keith Back To Top

 Hello, Ahmad.

 

Please forgive my ignorance, but I am an Excel neophyte. I have no idea how to "rewrite the select statement." Can you point me in the right direction?

 

Thanks for your patience.

 

Keith

 


Tuesday, January 02, 2018 - 4:12:18 AM - Ahmad Yaseen Back To Top

 Hello Keith,

Please try to rewrite the select statement to have the below change on the columns that failed to merge:

replace(replace(replace(@text, char(13), ''), char(10), ''), char(9), '') 

Best Regards,
Ahmad

 


Friday, December 29, 2017 - 3:28:21 PM - Keith Back To Top

 I'm not sure that I have the same problem, but I'm hoping you can help.

 

When I set up my spreadsheet, I used a previous spreadsheet as a template and merged two columns into one. Now when I insert a line into the spreadsheet, the previously-merged cells are again split and I have to manually merge them. Is there any way to correct this so that I can insert a line with the same column cells as the rest of the spreadsheet?

 

Thanks in advance.

 


Wednesday, November 15, 2017 - 7:42:30 AM - Ahmad Yaseen Back To Top

 Thank you Eduardo for your input here.

This should be applied for all SSMS versions starting from 2012. It depends on the data itself that you are exporting.

 

Best Regards,

Ahmad

 


Monday, November 13, 2017 - 5:37:49 PM - Eduardo Back To Top

 Wow, this is awesome!

I've lived with this issue for a long time... about 2 or 3 months and tried REPLACE function too, but using '\r' and '\n'. Never worked.

Where did you get this? It's like you knew someway... However thanks alot!

 

PS. I have some friends that work with SSMS 2016 (as me) but they don't get this error. Maybe you know what's going on here?

 


Monday, October 16, 2017 - 12:04:26 PM - Gramatik Back To Top

Much appreciated, thank you


Wednesday, October 04, 2017 - 7:22:56 PM - Christine Lowe Back To Top

Much appreciated!  Incredible assistance for something I believe to be unrelated, but in using your scripting assisted me in resolving user errors.

We just upgraded our MS accounting software residing in (3 SQL environments), which of course required SQL upgrade, starting on 2012, and 2014 and ending all on 2017.  I had several 3rd party accounting software company dataabase data tables holding extensive text actually corrupt during the ugrade process and cause a complete stop to some very time sensivtive procedures.  I was able to utilize your article and scripting as a base of understanding to correct the issues and we are back up and running.  Not just good info for developers, but for ERP System Administrators as well.  :-)  Many thanks!  Christine

 


Thursday, September 07, 2017 - 2:18:01 PM - sq Back To Top

Great Great Great all around! I was going crazy over this issue! Thank you and much apprecaite it!

 


Tuesday, July 11, 2017 - 2:21:56 PM - Brenda Laird Back To Top

This tip DEFINITELY helped...took care of it just like that!  Had searched for a while for other solutions, but this one did the trick!  Thank you so much!!


Wednesday, June 21, 2017 - 6:00:11 AM - Ahmad Yaseen Back To Top

 

 Hi Yara,

 

Thank you for your input here.

Please note that the bullet in ASCII code http://www.petefreitag.com/cheatsheets/ascii-codes/ is char(149). So please try using the below to eliminate the bullet:

SELECT replace(replace(replace(replace(CAST(YourField AS NVARCHAR(MAX)), char(13), ''), char(10), ''), char(9), ''),char(149),'') 

 

Best Regards,

Ahmad

 


Sunday, June 18, 2017 - 7:03:30 AM - Yara Elghoneimy Back To Top

Hello,

this solution solve the new line isssue -Thanks- , but i face another issue if data contains bullets (Rich Text) 

the bullet was saved in a cell and the text saved in another cell

Is there any Solution?

Thanks, 

 


Tuesday, June 13, 2017 - 1:29:09 PM - Eduardo Back To Top

 

 Thank you! This issue was driving me crazy using VLoopUp over imported tables in Excel.


Wednesday, April 26, 2017 - 6:25:41 AM - Woraluk Back To Top

 Thank you so much, 

it's good and work for me ^^

 

 


Monday, April 24, 2017 - 12:06:14 PM - Daniel Back To Top

 Thank-you.  This worked perfectly.

 


Tuesday, March 28, 2017 - 3:14:25 PM - Ify Back To Top

Thanks!!


Tuesday, March 28, 2017 - 1:51:44 AM - Oren Webb Back To Top

 Thanks!


Tuesday, February 28, 2017 - 4:02:56 AM - Per B Back To Top

 Thank you! Really helpful! 

 


Thursday, January 12, 2017 - 2:54:00 PM - LLS Back To Top

 Thank you, Thank you!

 


Friday, December 02, 2016 - 1:44:19 AM - Akram Shaikh Back To Top

 Thank you.. It really helped me.. 

 


Tuesday, November 29, 2016 - 6:24:09 PM - Kevin Back To Top

Handy tip, but if you're exporting tables and have to edit the SQL for every affected column the export tool quickly becomes useless. I found that creating a data connection in Excel allows importing multiple tables without the chr(13),chr(10) issue.

 


Thursday, November 17, 2016 - 7:52:19 PM - Vic Back To Top

 This is a lifesaver! Thank you so much for the tip!

 


Tuesday, November 08, 2016 - 11:27:38 AM - GP Van Eron Back To Top

 Thank you.  Very useful tip

 


Tuesday, October 25, 2016 - 2:01:55 AM - Abhishek Back To Top

Glad that i found this post. This was really helpful. The same logic worked for Sybase database, in my case, by replacing "Replace" function with "str_replace" compatible with sybase. 

 


Friday, October 14, 2016 - 12:51:29 PM - L Garcia Back To Top

This to me was very helpful.


Tuesday, October 04, 2016 - 6:06:30 PM - Martin Back To Top

 This helped - but I also had to replace Tabs (char(9) - as Steve G pointed me to)...


Thursday, September 08, 2016 - 10:42:46 PM - Fawaz Chughtai Back To Top

 for SQL server 2012+

SELECT replace(replace(CountyCode, char(10), ''), char(13), '') AS CountyCode FROM [MSSQLTipsDemo].[dbo].[CountryInfo]

And if CountyCode is a "nText" type then:

SELECT replace(replace(CAST(CountyCode as nvarchar(MAX)), char(10), ''), char(13), '') AS CountyCode FROM [MSSQLTipsDemo].[dbo].[CountryInfo]

 


Thursday, August 04, 2016 - 2:45:09 AM - Manish Saini Back To Top

 

 After 30 character space then line break in query in sql server


Tuesday, July 12, 2016 - 5:22:40 AM - Bartolome Madriaga Back To Top

Thank you very much. Very helpful. 

 


Thursday, June 16, 2016 - 5:25:58 AM - Rahul Back To Top

Thanks it works for most of the cases but i have scenario where it is failing and goes to new line. Seems there are few char that need replcaement.

 


Wednesday, March 16, 2016 - 12:50:47 AM - Santhanakrishnan Back To Top

Thanks for this Tip...

 


Thursday, March 03, 2016 - 10:25:44 AM - Alejandro Back To Top

 Muchas gracias, me sirvió mucho el Query, con esto logre solucionar mi problema para exportar datos a Excel desde SQL.

Gracias!!!

 


Monday, February 29, 2016 - 10:44:06 AM - Peter Tai Back To Top

 

Thank you for the clear description of the problem, the root cause and the solution. This saved me some time for sure!


Wednesday, February 24, 2016 - 5:14:48 AM - prebashni reddy Back To Top

 thank you for this. i struggled with this for a while until i decided to search for a solution online

 


Tuesday, January 26, 2016 - 3:57:20 PM - Soran Back To Top

 

 

Excellent solution, helped me.


Thursday, October 29, 2015 - 1:47:43 PM - Ahmad Yaseen Back To Top

Thank you all for your comments ... Happy to hear that this is helpful.

Best Regards,

Ahmad


Thursday, October 29, 2015 - 8:17:42 AM - Tommy Larsen Back To Top

Thanks, this post saved me getting a lot of gray hair :)


Tuesday, October 27, 2015 - 9:56:16 AM - Miika Kontio Back To Top

Thank you very much. Saved me a lot of time


Tuesday, September 29, 2015 - 7:50:50 AM - Ashwin Back To Top

While this works, in a situation where the field holds data that needs the carriage returns to be preserved for formatting purposes, by doing this we would loose this formatting.

While doing imports exports, It would be a better idea to find and replace the carriage return with some special character e.g. $%$ then after the data is imported replace the same with carriage return.


Tuesday, September 22, 2015 - 1:08:26 PM - Sunde Back To Top

Great solution.  Solved the problem for me.  Many thanks.


Thursday, July 30, 2015 - 11:34:37 AM - Dave Back To Top

You solved me a problem that I struggle with more than 6 hours! Thank you a lot!


Monday, April 20, 2015 - 3:04:31 PM - gorakh Back To Top

Nice Artical .. This helps me .. Thanks


Tuesday, February 24, 2015 - 5:32:36 AM - Ahmad Yaseen Back To Top

Thanks Steve for your comment.

 

A small note that if the field type is TEXT or nTEXT you need to convert it to NVARCHAR(MAX) first before applying the replace as below:

replace(replace(CAST(YourField AS NVARCHAR(MAX)), char(10), ''), char(13), '')  as Field1


Regards,

Ahmad Yaseen


Thursday, February 19, 2015 - 11:50:55 AM - Steve G Back To Top

I see this is SQL Server 2008. My solution is to use a function to do this type of cleanup. As a scalar-valued function, this runs very quickly.

 

create function [dbo].[textcleanup] (@text as varchar(max))
returns varchar(max)
as begin
return replace(replace(replace(@text, char(13), ''), char(10), ''), char(9), '') 
end
 

Friday, January 16, 2015 - 4:10:42 PM - Mohamed Back To Top

With OpenOffice this working fine for me without a need to drop the carriage return

Thanks


Thursday, January 15, 2015 - 1:52:51 AM - Ahmad Yaseen Back To Top

Thanks Jeff for your comment.

Yes, more work but cleaner data ^_^

 

Best Regards,

Ahmad Yaseen

 

Thursday, January 15, 2015 - 1:50:44 AM - Ahmad Yaseen Back To Top

Thanks Glenn for your comment.


Wednesday, January 14, 2015 - 4:28:41 PM - Jeff Bennett Back To Top

It's a feature!

Why do all new features mean more work? 

Thanks for the write-up. 

 

Jeff Bennett

St. Louis, MO


Wednesday, January 07, 2015 - 4:31:45 PM - Glenn Scamman Back To Top

I noticed a variation of this issue recently with SQL Server 2008 and an internal application we use.  The application retrieves data from a SQL Server 2008 database.  If data contains carriage returns, you don't really notice it in the application due to the way the software renders (or ignores) carriage returns in a grid.  However, when we save that data directly to an Excel file, the carriage returns are ignored.  But if we save that data as a tab-delimited text file, and then later open that text file with Excel (using the Excel Import wizard), the carriage returns are preserved, throwing the Excel file into disarray.

I was going to have to find a way to strip out the carriage returns inside our application.  Your technique will make the task pretty simple, doing it as part of the original query versus immediately prior to saving the data to a file.  Thanks!



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools