Removing Line Breaks from SQL when coping to Excel

Recently I did what I often do which is run a select statement in SQL and then copy and paste the results including headers into excel so I can create reports and pivot tables. Problem was that when I did this the text from certain SQL columns was breaking onto a new lines. This made it impossible to use the data and quickly became frustrating when trying to fix the issue within Excel. 

It just so happens that we had upgraded to SQL Server 2012. Researching the issue the reason thus started to happen was that versions prior to SQL Server 2012 used to omit the carriage return (\n\r). SQL Server 2012 and more recent editions including 2014 and 2016 preserve cararge returns which is why pasting into excel creates this issue. 

To demonstrate this issue and how to fix it lets have a look at some SQL and the data it returns. 

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ID]
	  ,[Content]
      ,[ArticleID]
      ,[AcceptedAnswer]
  FROM [IntermittentBug_Dev].[dbo].[tbl_Answer]

The SQL above will return 2 test answers from IntermittentBug. The column "content" is a XHTML string and as a result has many carriage returns. When I copy this with headers into Excel I get the following. 

coping into Excel creates this shambles...

Thankfully we can fix this relatively easily. Rather than trying to add a fix within Excel, you can do it in SQL. 

For the column that contains the carriage returns add the following SQL... 

replace(replace([Content], char(10), ''), char(13), '') AS Content

full SQL script

SELECT TOP (1000) [ID]
	  ,replace(replace([Content], char(10), ''), char(13), '') AS Content
      ,[ArticleID]
      ,[AcceptedAnswer]
  FROM [IntermittentBug_Dev].[dbo].[tbl_Answer]

Then when you copy this into excel, its all in rows as expected. 


JGilmartin Profile Image

JGilmartin

Technical Architect at Pinewood Technologies

Rating: 2890

C# Expert

Offline


Useful Tip Statistics
  • Views: 4174
  • Comments: 0
  • Author: JGilmartin (2890)
  • Date: 29/1/2017 19:20
Tags
SQL Server SQL

© 2016 - 2018 - IntermittentBug