Line Split Issues when Copying Data from SQL Server to Excel

By:   |   Comments (77)   |   Related: > 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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




Monday, July 15, 2024 - 4:07:39 PM - Greg Robidoux Back To Top (92391)
Hi Tyler,

Does this happen for every record? Are there any other control characters in the strings?

Take a look at this article: https://www.mssqltips.com/sqlservertutorial/9347/sql-ascii-function/

There is a function that will print out the ASCII values for each character in a string.

Monday, July 15, 2024 - 3:45:48 PM - Tyler Chase Back To Top (92390)
What if this still happens even if I'm using the line feed - Char(10) - and not the carriage return?

Wednesday, September 28, 2022 - 5:49:39 AM - Raj Back To Top (90530)
Simply great. Working fine. thanks.

Monday, September 12, 2022 - 5:03:53 PM - Richard Back To Top (90466)
This isn't workable if one WANTS the linefeeds in the cell in Excel. For example, I have a table with a text cell containing a log, with separate lines. I have the option set to allow line breaks in grid result in SSMS, which works fine, and when pasted from SSMS into Excel, I want a multi-line cell. So far, no luck.

Thursday, July 14, 2022 - 8:45:32 AM - Sk Abdul Rahim Back To Top (90262)
This is very help full.
Thanks for the solution, I also had the same problem and you solution is perfect.

Wednesday, February 17, 2021 - 7:40:05 AM - HanShi Back To Top (88254)
It is NOT necessary to rewrite the query, this behavior is just a setting in newer versions of SSMS.

Select "Tools - Options" from the SSMS menu and navigate to "Query results - SQL Server - Results to Grid". Clear the checkbox for "Retain CR/LF on copy or save" to get a multi-line result as a single line.

Thursday, January 21, 2021 - 3:39:21 AM - Mpho Back To Top (88077)
Thanks for the solution, I also had the same problem and you solution is perfect.

Tuesday, April 14, 2020 - 10:53:06 AM - Evgeny Ponamarev Back To Top (85364)

Thanks a lot, Ahmad.

I have the same problem and couldnt get any reasonable answer in the internet. Your method works perfectly.


Thursday, December 12, 2019 - 4:44:26 PM - Shell Back To Top (83410)

Unfortunately this doesn't help when you want to retain the carriage returns in excel so that the field in excel appears as multi-line text in 1 cell, not multiple columns in excel, replacing with a space or a blank string obviously won't fix this.


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

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 (82041)

Great tip. Saved my hours of research. 


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

Thank you very much.


Friday, December 7, 2018 - 8:43:06 AM - Harish Back To Top (78424)

this was really helpful; thanks a lot buddy. 


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

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 (78217)

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 (78073)

 

 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 (77968)

 Thanks, this one was very useful!👍🙂

 


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

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 (77752)

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 (75696)

 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 (75680)

 

 

Thanks. it worked for me.


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

 

 

 

This is an absolutely fantastic answer.


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

 

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 (75297)

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 (75050)

Hi Ahmad,

This is very helpful.

Thanks.


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

 Thank you, 

Amazingly the first result of google search, it worked perfect 


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

Hi Ahmad

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

 

Terence


Thursday, January 4, 2018 - 10:54:33 AM - Keith Back To Top (74767)

 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 4, 2018 - 1:50:15 AM - Ahmad Yaseen Back To Top (74737)

 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 2, 2018 - 7:59:47 PM - Keith Back To Top (74687)

 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 2, 2018 - 4:12:18 AM - Ahmad Yaseen Back To Top (74661)

 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 (74578)

 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 (69766)

 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 (69687)

 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 (68439)

Much appreciated, thank you


Wednesday, October 4, 2017 - 7:22:56 PM - Christine Lowe Back To Top (66908)

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 7, 2017 - 2:18:01 PM - sq Back To Top (66015)

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 (59254)

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 (57851)

 

 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 (57612)

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 (57369)

 

 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 (55200)

 Thank you so much, 

it's good and work for me ^^

 

 


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

 Thank-you.  This worked perfectly.

 


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

Thanks!!


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

 Thanks!


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

 Thank you! Really helpful! 

 


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

 Thank you, Thank you!

 


Friday, December 2, 2016 - 1:44:19 AM - Akram Shaikh Back To Top (44881)

 Thank you.. It really helped me.. 

 


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

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 (43796)

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

 


Tuesday, November 8, 2016 - 11:27:38 AM - GP Van Eron Back To Top (43724)

 Thank you.  Very useful tip

 


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

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 (43566)

This to me was very helpful.


Tuesday, October 4, 2016 - 6:06:30 PM - Martin Back To Top (43496)

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


Thursday, September 8, 2016 - 10:42:46 PM - Fawaz Chughtai Back To Top (43285)

 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 4, 2016 - 2:45:09 AM - Manish Saini Back To Top (43049)

 

 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 (41868)

Thank you very much. Very helpful. 

 


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

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 (40947)

Thanks for this Tip...

 


Thursday, March 3, 2016 - 10:25:44 AM - Alejandro Back To Top (40854)

 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 (40817)

 

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 (40771)

 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 (40508)

 

 

Excellent solution, helped me.


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

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 (38986)

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


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

Thank you very much. Saved me a lot of time


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

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 (38726)

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


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

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 (36990)

Nice Artical .. This helps me .. Thanks


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

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 (36290)

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 (35973)

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 (35949)

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 (35948)

Thanks Glenn for your comment.


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

It's a feature!

Why do all new features mean more work? 

Thanks for the write-up. 

 

Jeff Bennett

St. Louis, MO


Wednesday, January 7, 2015 - 4:31:45 PM - Glenn Scamman Back To Top (35862)

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!















get free sql tips
agree to terms