Excel – SQL output not pasting into Excel properly

microsoft excelsql server

Very strange problem.

I design queries in T-SQL and frequently copy the output which appears in the "results" tab into Excel in order to scrutinise/analyse the results/check for mistakes etc.

Recently, I've noticed that on a number of occasions, when I select the output data in the T-SQL "results" tab, right-click, copy, and then right click and paste it into Excel, the output doesn't paste properly. For example, today, I am pasting the SQL output into an Excel spreadsheet, and two of the cells in column "A" in Excel contain no value. But when I look at the copied data in Excel, there definitely is a value in column A. There are a number of other similar copying errors.

Has anyone else ever encountered this? I've never heard of this happening before. But it has happened several times now over the course of several months.

This is not a trivial problem, as the consequences of this could, obviously, be quite significant.

Best Answer

A couple things could cause this.
1) Non-printable characters in your data. To verify this, route the output to a text file and open that file in a text editor that shows the hex codes. Removing non-printable characters in SQL is covered in many StackExchange questions. Example question and solution.


2) Excel is remembering past behavior or formatting and applying it to the current data. To fix this, close all instances of Excel and start fresh.

EDIT based on comment:
To remove the extra CRLF, try something like this:

Declare @CR as Varchar
Declare @LF as Varchar
Declare @CRLF as Varchar(2) 
Set @CR = CONVERT(VARCHAR, CONVERT(VARBINARY, '0x0D', 1))
Set @LF = CONVERT(VARCHAR, CONVERT(VARBINARY, '0x0A', 1))
Set @CRLF = @CR + @LF

Select Replace(Columnname, @CRLF, '')

This is limited to the CRLF only. If your data has more hex characters, you will need something more robust.

Related Question