Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 work into a seperate 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

share|improve this question
add comment

4 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
yes you're right - we've cracked it - thanks –  Paul May 23 '12 at 9:24
add comment

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
share|improve this answer
    
Thanks for the reply - i'll take a look –  Paul Mar 19 '12 at 11:37
add comment

Before pasting the results: Data → Text to Columns

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

Finally paste the results.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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