1

I'm attempting to copy a query result from SSMS to excel. I selected "copy with headers" and pasted it into Excel. My data set has 9 columns. When I paste the data into excel, information from column 9 ends spread across columns 9, 10 and 1 It looks like this:

A B C D E F G H I    -Column Heading

A B C D E F G H I I  -Data

I

(blank row)

I've reviewed the query results in SSMS and this is not occurring in the original data. When the value in column F is NULL the additional row and information in column 10 do not occur. Thus far I have tried the following:

-When I remove column 9 from the query then copy & paste, column 8 is spread across 8, 9, and 1.

-I've also created a brand new spreadsheet, made sure to clear any formatting and tried the copy & paste.

-I saved the query results as a .csv file and imported it into Excel. I still got the same result.

-I copied the columns individually one at a time. The the information in the 8th column ends up on two lines paired with the other columns of the next row. So each item in column 8 becomes another row offset downwards until there are many more values in column 8 than other columns. Where the value in column 8 is NULL, this does not occur.

-I removed all the other items from the query result so that only the values of columns 8 and 9 are returned. All information from column 9 ends up in column 8 followed by a blank row.

  • Returning 8 alone, each item returned ends up on two rows.

  • Returning 9 alone, the data is pasted correctly.

The headers are always in the right place. From what I can surmise, the data in column 8 is the culprit here. The data type is a varchar(max) which allows nulls. The information included is in the following format, (TC Date & Time, Last Name, First Name) Comments

Moving SSMS query results into Excel to make tables is something I do frequently. However I have never before encountered this result. Hopefully my explanation is thorough enough so someone can tell me how to correct this error. Thanks!

Greymalk
  • 17
  • 1
  • 4
  • 2
    Sounds like your data has line feed/carriage returns and/or tabs in that column. Maybe try making a temp table and following these steps to replace those characters will help you https://www.mssqltips.com/sqlservertip/3416/line-split-issues-when-copying-data-from-sql-server-to-excel/ Then export from your temporary table where you cleaned the data instead of the original. – Jacob H Nov 28 '17 at 14:24
  • Can you make your question reproducible by providing a sample query? – Tim Biegeleisen Nov 28 '17 at 14:24

3 Answers3

3

Replace feed and Carriage returns from your dataset before you can paste into Excel, Try something like this on the columns you are having issues and then try to paste it in excel:

SELECT REPLACE(REPLACE(yourcolumnname, CHAR(13),' '), CHAR(10),' ')
FROM table
Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20
0

This is probably due to using 'Text to columns' recently in Excel. That splits columns using some rule. Columns need to be set back to 'tab delimited'.

For the offending column:

  • Data → Text to Columns
  • Original Data Type: Check Delimited
  • Click Next
  • Delimiters: check 'Tab', uncheck anything else.
  • Click Next
  • Click Finish
Zorkolot
  • 1,899
  • 1
  • 11
  • 8
0

SSMS copy-paste does not preserve data types. Excel tries to parse the string and splits it into additional columns or even lines. I develop SSMSBoost add-in and we have covered this in our video, which explains 3 different ways of exporting the data into Excel without data loss (data type information is preserved): (Copy-Paste in native excel format, XML export, .dqy Query) https://youtu.be/waDCukeXeLU

Andrei Rantsevich
  • 2,879
  • 20
  • 25
  • I love this SSMS extension. @Andrei, is a new version is being worked on, particularly now that SSMS 19 has been released. – JamesP Feb 03 '23 at 10:57