Line Break Issue when Copying from SSMS 2012 & Pasting into Excel

Tüm Yanıtlar

• 

  

  3

  Oylamak İçin Oturum Aç

  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] 

  
  

  • Yanıt Olarak Öneren Fanny Liu 5 Şubat 2013 Salı 09:35
  • Yanıt Önerisini Geri Alan brgnewman 6 Şubat 2013 Çarşamba 20:19

  4 Şubat 2013 Pazartesi 16:28
• 

  

  1

  Oylamak İçin Oturum Aç

  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.

  ---

  Satya SKJ, Moderator - SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
  Author of SQL Server 2008 R2 Administration cookbook.
  Follow me @sqlmaster.

  • Yanıt Olarak Öneren Fanny Liu 11 Şubat 2013 Pazartesi 09:15
  • Yanıt Olarak İşaretleyen Allen Li - MSFT 21 Şubat 2013 Perşembe 08:45
  • Yanıt İşaretini Geri Alan brgnewman 11 Haziran 2013 Salı 18:44

  7 Şubat 2013 Perşembe 13:50
• 

  

  2

  Oylamak İçin Oturum Aç

  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? 

  21 Şubat 2013 Perşembe 22:53
• 

  

  1

  Oylamak İçin Oturum Aç

  Same issue, bump

  9 Ağustos 2013 Cuma 21:39
• 

  

  0

  Oylamak İçin Oturum Aç

  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

  5 Kasım 2013 Salı 16:03
• 

  

  0

  Oylamak İçin Oturum Aç

  You can open a Connect bug report:

  https://connect.microsoft.com/SQLServer

  ---

  Kalman Toth Database & OLAP Architect
  New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
  

  
  

  
  

  • Düzenleyen Kalman TothEditor 10 Kasım 2017 Cuma 15:35

  13 Şubat 2014 Perşembe 15:58

  Yanıtlayıcı
• 

  

  2

  Oylamak İçin Oturum Aç

  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).
  

  
  

  9 Haziran 2014 Pazartesi 18:08
• 

  

  0

  Oylamak İçin Oturum Aç

  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.
  

  • Yanıt Olarak Öneren Peter A Grigg 10 Haziran 2014 Salı 19:52

  10 Haziran 2014 Salı 19:50
• 

  

  0

  Oylamak İçin Oturum Aç

  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?

  18 Haziran 2014 Çarşamba 20:50
• 

  

  0

  Oylamak İçin Oturum Aç

  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.

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  18 Haziran 2014 Çarşamba 21:26
• 

  

  0

  Oylamak İçin Oturum Aç

  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. 
  

  11 Mayıs 2015 Pazartesi 21:36
• 

  

  4

  Oylamak İçin Oturum Aç

  This is an old topic, but I think there is an implemented solution for your problem in the query options in the SSMS.
  

  Actually one can make SSMS to retain line breaks on copy or save.

  It can be done even in the current query window:

  1. Right-click in the query area in SSMS,
  2. choose "Query Options",
  3. in the "Results" tree choose "Grid",
  4. and on the right check "Retain CR/LF on copy or save"

  Now run the query and try to copy&paste the result. 

  You can do it also through a "Tools" -> "Options" menu in the menu bar in SSMS, but if you do so, it will work only in the newly opened query window.

  ---

  Best regards divx

  
  
  
  
  
  

  • Yanıt Olarak Öneren lastpyro 5 Ağustos 2019 Pazartesi 06:52
  • Düzenleyen Bartosz Siemasz 7 Eylül 2019 Cumartesi 22:00

  3 Ekim 2018 Çarşamba 19:34
• 

  

  0

  Oylamak İçin Oturum Aç

  Thank you, Bartosz Siemasz!! That saved my day 
  

  • Düzenleyen lastpyro 5 Ağustos 2019 Pazartesi 06:56 added name

  5 Ağustos 2019 Pazartesi 06:52