Home / server / Questions / 277742
Accepted
adolf garlic
Asked: 2011-06-08 00:14:06 +0800 CST

Results copied from SSMS splitting across columns when pasted into Excel

  • 772

SSMS 2008 -> Excel 2003

This isn't happening all the time, but sometimes when I 'copy with headers' from sql server management studio, the results in Excel look as though I've used the 'text to columns' wizard with 'spaces' and 'brackets' as delimiters, meaning that the results become raggedly distributed across columns making them useless

I've looked in the 'grid output' query options but can't see anything which may be causing the issue

(not sure if this should be on superuser?)

ssms microsoft-excel

4 Answers

  1. Best Answer

    It is persisting 'text to columns' settings from earlier

    Workaround:
    Find an empty cell
    put asdf
    in that cell

    Data|Text to columns
    delimited
    uncheck all the options
    finish up

    • 2

  2. This problem can be caused by Excel "Text to column" feature. What it does is, it breaks a single column into multiple columns depending on which delimiter you choose. Once you use this feature, Excel somehow thinks it is still on and may affect your next copy and past operation.

    To fix Go to Data > Text To column and select a different delimiter (other than space), such as comma or a tab. Then copy and paste. It should fix the problem.

    Related weird question mark in Excel after copy and past

    • 2

  3. This is a common error in such process -- copy from SSMS and paste to Excel. This issue occurs when one of the columns in the SSMS result has a dot (.) and treats it like a separate row and the result could be a messy column/row in Excel. For example: 'Heineken Bottled Beer .33 Liter'. That dot in front of the 33 can throw off the pasting process for the same reason stated above.

    This is how to avoid it without any code.

    1. Copy all the columns that do not have the dot (.) in its records of the result.
    2. Paste it into Excel.
    3. Do the 1st step if there is more columns after the column that has the dot(.).
    4. Finally copy and paste the column that has the dot(.) at the end*.
      • If direct paste to Excel for that column does not work, try to paste it into a new sheet of Excel, then copy into the wherever you want to patse it to.

    Hope it helps.

    • 0

  4. I develop SSMSBooad add-in for SSMS (www.ssmsboost.com) and we have addressed this topic there: you can use "Copy data as XML Spreadsheed (Excel)" function, that uses native Excel clipboard format to pass headers and datatype information to Excel. Hope this helps.

    Update: recently, we have created a tutorial video on this topic:

    https://www.youtube.com/watch?v=waDCukeXeLU

    • 0