Line Break Issue when Copying from SSMS 2012 & Pasting into Excel
Bit of a weird issue here...
With SSMS 2008 R2, when I copy Query Data and paste it the data in Excel, it pastes completely fine into Excel (rows & columns all line up).
With SSMS 2012, when I copy the exact same Query Data and paste the data in Excel, it creates new line breaks and splits the data between multiple rows.
(Same issue described @ http://stackoverflow.com/questions/13380779/copy-and-paste-from-sql-server-management-studio-2012-new-line-issue-into-excel)
Any suggestions or thoughts?
February 4th, 2013 11:01am
It sounds like a new feature in SSMS 2012. You get the carriage return if it is on your data, otherwise you don't get it.
The solution would be to replace the carriage return in SQL statement.
select top 10 replace([column], char(10) + char(13), ' ') as [struff]
-
Proposed as answer by
Fanny LiuMicrosoft contingent staff, Moderator
Tuesday, February 05, 2013 9:35 AM
-
Unproposed as answer by
brgnewman
Wednesday, February 06, 2013 8:19 PM
February 4th, 2013 4:28pm
It sounds like a new feature in SSMS 2012. You get the carriage return if it is on your data, otherwise you don't get it.
The solution would be to replace the carriage return in SQL statement.
select top 10 replace([column], char(10) + char(13), ' ') as [struff]
February 4th, 2013 7:28pm
Do you have any QUOTES within that data?
Because Excel treats double quotes differently. if its a frequent operation you might build a package to export/import data between the tools.
February 7th, 2013 4:50pm
We have the same issue here. In SQL Server 2008 R2 the results of a query copy over just fine to Excel. The same query run in SQL Server 2012 produces some records that have a line break in the middle of the record. There are no line breaks or quotes in
any fields in the record. It also happens if we save the results as a .CSV file.
Is this a bug in SQL 2012?
February 22nd, 2013 1:53am
Same issue, bump
August 9th, 2013 5:41pm
Same here. But I have combinations of char(13)+char(10), char(10) and char(13) so it's a nightmare to keep on top of
November 5th, 2013 4:03pm
February 13th, 2014 3:58pm
I was having this same issue. I overcame it by thinking of it more from the excel perspective.
Wrap your columns with line breaks in them with double quotes "
SELECT col1, '"' + columnWithLineBreaks + '"', col3 FROM table
This tells excel to keep going until the closing quote for the cell (think CSV).
June 9th, 2014 6:08pm
The real answer is a combination of the above answers:
SELECT col1, '"' + replace(columnWithLineBreaks,'"', '""') + '"', col3 FROM table
This allows for Quotes and doesn't require losing the CR/LFs or LF/CRs.
Thanks to those above, because I couldn't figure this out until I saw your answers.
-
Proposed as answer by
Peter A Grigg
Tuesday, June 10, 2014 7:52 PM
June 10th, 2014 7:50pm
bump
I have this same issue and while Peter's solution is very good. It is only good for a single query. I have HUNDREDS of saved queries and multiple users of the same. I have combed through ALL the options/settings and, obviously, there is nothing there. This
is a real PIA mostly because we are dealing with some resultant sets that are in excess of 1mil lines. Does anyone have any idea why this is happening in 2012 and did not happen in 2008? Other than rewriting the many hundreds of queries, does anyone have any
idea how to resolve it?
June 18th, 2014 8:50pm
So you are exporting data from SQL Server by means of Copy-Paste from SSMS?
As a short-term solution stick to SSMS 2008.
As longer-term solution look at the Import/Export Wizard or even be brave to look at SQL Server Integration Services. (He said, despite that he does not know SSIS himself.)
Since I often copy queries from trace output or DMVs, I very much appreciate that SSMS 2012 retains the line breaks.
June 18th, 2014 9:26pm
This does not solve having to rewrite past queries, but going forward, putting the corrective SQL code in a function helps. whenever you are subsequently SELECT-ing a column you suspect may contain line breaks, like a notes or free-text field, then
select the function, with the suspect column as input.
May 11th, 2015 5:37pm