CODEWITHSUNDEEP
sql-serverexcel
Greymalk
Greymalk

Reputation: 17

Copy & Paste Errors When Moving Data From SSMS to Excel

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.

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!

Upvotes: 1

Views: 5145

Answers (3)

Andrei Rantsevich
Andrei Rantsevich

Reputation: 2935

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

Upvotes: 0

Zorkolot
Zorkolot

Reputation: 2027

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

Upvotes: 2

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

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

Upvotes: 4

Related Questions