15

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

21

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.

| improve this answer | |
  • yes you're right - we've cracked it - thanks – Paul May 23 '12 at 9:24
  • It worked. Thanks. – Sagar Shirke Aug 13 '19 at 11:09
7

Before pasting the results: Data → Text to Columns

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

Finally paste the results.

| improve this answer | |
1

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
| improve this answer | |
1

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.

| improve this answer | |
  • I think some of the excel that you have in the system, changed the default options. So, I think instead of messing around with config or data transformation is easy to close all and open it again. – Juan Acosta Feb 20 '19 at 0:11
0

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.

| improve this answer | |
0

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), '')
| improve this answer | |
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.