Line Split Issues when Copying Data from SQL Server to Excel

By: Ahmad Yaseen   |   Updated: 2015-01-01   |   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:

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

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:

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.

About the author

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