SQL output not pasting into Excel properly

Question

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.

Solution

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.

Comments

[Admin]

The problem in this specific case has been identified. The original data source had an "enter" character caused by a user inputting it. This caused the line to split whenever that name was reached, which was twice in this particular row, causing the two-line gap. I have nothing to do with the administration of this database or the input of the data. However, I have noticed different copying errors in the past, which I don't think are explained by this explanation. Unlike this one, they typically disappeared when I copied and pasted again on a new iteration.

[Statsanalyst]

Thanks for your reply. I've identified the cause in this specific situation (see my comment above under my question) but have encountered other errors which are not explained by this explanation. I will definitely consider your two suggestions if I encounter any further issues.