Line Split Issues when Copying Data from SQL Server to Excel
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?
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:
- 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
View all my tips
Article Last Updated: 2015-01-01