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

Question

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 I can happily copy (right-click > copy) and then just right-click and paste into an Excel spreadsheet. This is no problem.

On another computer, 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.

Answer 1

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.

Answer 2

Before pasting the results: Data → Text to Columns

• Original Data Type: Check Delimited
• Next
• Delimiters: Uncheck Space
• Next
• Finish

Finally paste the results.

Answer 3

Have you tried replacing the Excel startup templates on the computer that has the problem? It may have a macro defined that is being triggered by your paste operation. According Microsoft Knowledge Base Article 924460, the templates are stored under the following locations:

By default, user templates files are stored in the following location:

• In Windows XP C:\Documents and Settings\\Application Data\Microsoft\Templates
• In Windows Vista or in Windows 7 C:\Users\\AppData\Roaming\Microsoft\Templates

Answer 4

I've found that if I exit Excel, and then restart it, and reload my spreadsheet, it clears the text to columns options, so the paste will work as expected again.

Hector's answer is nice in that you don't have to exit Excel, but this works as well, and is pretty quick and easy.

Answer 5

If your table contains an nvarchar(max) field move that field to the bottom of your table. In the event the field type is different to nvarchar(max), then identify the offending field or fields and use this same technique. Save It. Reselect the Table in SQL. If you cant save without an alter you can temporarily turn of relevant warnings in TOOLS | OPTIONS. This method carries no risk. Copy and Paste the SQL GRID display with Headers to Excel. The data may still exhibit a carriage return but at least your data is all on the same row. Then select all row records and do a custom sort on the ID column. All of your records should now be intact and consecutive.

Answer 6

A similar situation I encounter from time to time is rows being split into multiple rows when users have use a carriage return/line feed character in a column intended for comments. I found a solution that works for SQL 2012/2014 here.

This is, apparently, not an issue in previous versions of SQL Server. If you don't want to go through the link, here's how to fix the issue within your SQL:

REPLACE(REPLACE([YourColumnHere], CHAR(10), ''), CHAR(13), '')

Answer 7

Solution to this problem in Excel after pasting click option.

"Use Text import wizard" -> Delimited -> Next Check "Tab", check "Treat consecutive delimeter as one" -> Next Finish

Answer 8

This worked for me:

1. Export from sql to csv.
2. In Excel, go to the Data tab
3. Choose "From Text/CSV" (second ribbon option from the left).
4. The Import Data box will appear, locate and import your csv.
5. Your CSV will be displayed in Excel (prior to being inserted). Everything should be in the right columns.
6. Choose Load.
7. The data from the CSV will be inserted into Excel into the correct columns. From here, you can copy and paste values into a new blank spreadsheet if desired.

Hope this helps someone.