Line Split Issues when Copying Data from SQL Server to Excel

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.

Ahmad Yaseen

Ahmad has a Bachelor’s Degree in Computer Engineering from the University of Jordan and five years of experience working as a SQL DBA, gaining valuable knowledge of database structures, practices, principles and theories. His experience also includes.NET development, working with database applications, scripting and creating SQL queries and views. His personal abilities include having very strong communication and interpersonal skills, the ability to prioritize and to make good sound decisions that benefit the company. He has experience in upgrading, configuring, securing, tuning and monitoring SQL Servers since SQL Server 2005. This includes SQL Server performance tuning, SQL Server resource governor management, SQL Server maintenance plans, SQL Server data collection (Reports) analyzing and SQL databases design, developing, indexing and query optimization. In addition, he is familiar with installing and configuring SSRS, SSIS and SSAS. When it comes to disaster recovery and high availability, he has a solid foundation in SQL backup and recovery scenarios, mirroring, replication, log shipping, SQL clustering and AlwaysOn technology.

• MSSQLTips Awards: Author Contender – 2016-2017 | Trendsetter (25+ tips) – 2016 | Rookie Contender – 2015