Excel – Copy/Pasting data from SQL Server to Excel splits up text into multiple columns

copy/pastemicrosoft excelparsing

I've got a problem pasting data from the result grid of SQL Server 2005 to an excel 2007 spreadsheet.

I have a query in SQL Server that returns 2 columns (a number column and a text column)

On one computer here i can happily copy (right-click > copy) and then just right-click and paste into an excel spreadsheet. no problem.

On another computer here when i try and paste into excel it splits the text column up and pastes the text into multiple columns based on spaces between words.

For example if one of the rows has…

Paste me please

…in it then when pasting into excel it splits the text and pastes each word into a separate column within excel.

We've tried comparing options in both SQL Server & excel with the computer it works fine on but can see no differences.

Any ideas welcome

Thanks

Best Answer

If you've used Text to columns recently, it will apply those settings to the Paste operation. I don't remember this happening before Excel 2007, but it may have. I always have to go back and set it back to tab delimited before I start copying and pasting again.

Related Solutions

Excel – Pasting text into Excel without splitting on spaces (or other delimiter)

This seemed to work, but is a bit involved.

Enter data into a cell.
Select the Text to Columns feature.
Make sure Delimited is selected and choose Next.
Uncheck the check next to Space (or the delimiter you want to disable)
Click Finish.

Excel – SQL output not pasting into Excel properly

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.

Best Answer

Related Solutions

Excel – Pasting text into Excel without splitting on spaces (or other delimiter)

Excel – SQL output not pasting into Excel properly

Related Question