New Line Issue when copying data from SQL Server 2012 to Excel

Question

I recently upgraded to SQL2012 and am using Management Studio  One of my columns in the database has a CHAR 13    CHAR 10  stored in it    When I was using SQL Server 2008  this would copy and paste completely fine into Excel  Now  however  copying and pasting the same data creates a new line  carriage return in the data I have in Excel   Is there a setting I missed in SQL2012 that will resolve this issue  I don t want to simply REPLACE CHAR 13    CHAR 10   on every single database selection  as I would have to go from using SELECT   to defining each individual column

User · Answer

This sometimes happens with Excel when you've recently used "Text to Columns."

Try exiting out of excel, reopening, and pasting again. That usually works for me, but I've heard you sometimes have to restart your computer altogether.

User · Answer

If your table contains an nvarchar max  field move that field to the bottom of your table  In the event the field type is different to nvarchar max   then identify the offending field or fields and use this same technique   Save It   Reselect the Table in SQL   If you cant save without an alter you can temporarily turn of relevant warnings in TOOLS   OPTIONS  This method carries no risk  Copy and Paste the SQL GRID display with Headers to Excel  The data may still exhibit a carriage return but at least your data is all on the same row  Then select all row records and do a custom sort on the ID column  All of your records should now be intact and consecutive

User · Answer

you really could find out which rows   data has carriage returns and fix the source data   instead of just put a bandaid on it   UPDATE table Set Field   Replace Replace Field  CHAR 10         CHAR 13        WHERE Field like       CHAR 10        or Field like       CHAR 13

User · Answer

This is fixed by adding a new option Retain CR LF on copy or save under the Tools -  Options    menu  Query Results -  SQL Server -  Results to Grid    You need to open new session  window  to make the change take a place   The default is unselected  false  which means that copying saving from the grid will copy the text as it is displayed  with CR LF replaced with spaces   If set to true the text will be copied saved from the grid as it actually is stored - without the character replacement    In case people missed following the chain of connect items  leading to https   connect microsoft com SQLServer feedback details 735714   this issue has been fixed in the preview version of SSMS    You can download this for free from https   msdn microsoft com library mt238290 aspx  it is a standalone download so does not need the full SQL media anymore    Note - the page at https   msdn microsoft com library ms190078 aspx currently isn t updated with this information  I m following up on this so it should reflect the new option soon

User · Answer

My best guess is that this is not a bug  but a feature of Sql 2012   -  In other contexts  you d be happy to retain your cr-lf s  like when copying a big chunk of text  It s just that it doesn t work well in your situation    You could always strip them out in your select  This would make your query for as you intend in both versions   select REPLACE col  CHAR 13    CHAR 10         from table

User · Answer

As many times I have to copy data from SQL to excel  I ve created function to deal with with new line and also tab characters  which make shifts in columns after pasting to Excel    CREATE FUNCTION XLS  String NVARCHAR MAX     RETURNS NVARCHAR MAX  AS BEGIN     SET  String   REPLACE   String  CHAR 9            SET  String   REPLACE   String  CHAR 10            SET  String   REPLACE   String  CHAR 13            RETURN  String END  CREATE FUNCTION XLS  String NVARCHAR MAX    RETURNS NVARCHAR MAX   AS BEGIN     SET  String   REPLACE   String  CHAR 9            SET  String   REPLACE   String  CHAR 10            SET  String   REPLACE   String  CHAR 13            RETURN  String END   Example usage   SELECT dbo XLS Description  FROM Server Inventory

User · Answer

One less than ideal workaround is to use the 2008 GUI against the 2012 database for copying query results   Some functionality like  script table as CREATE  does not work  but you can run queries and copy paste the results into Excel etc from a 2012 database with no issues     Microsoft needs to fix this

User · Answer

The following  work-around  retains the CRLF and supports pasting data with CRLF characters into Excel without breaking column data into multiple lines   It will require replacing  select    with named columns and any double-quotes in the data will be replaced with the delimiter value   declare  delimiter char 1  set  delimiter        declare  double quote char 1  set  double quote        declare  text varchar 255  set  text    This  is  a test   -- This query demonstrates the problem   Execute the query and then copy paste into Excel  SELECT  text  -- This query demonstrates the solution  SELECT  double quote   REPLACE  text   double quote   delimiter     double quote

User · Answer

Once Data is exported to excel  highlight the date column and format to fit your needs or use the custom field  Worked for me like a charm

User · Answer

AHiggins s suggestion worked well for me   REPLACE REPLACE REPLACE B Address  CHAR 10         CHAR 13         CHAR 9

User · Answer

Changing all my queries because Studio changed version isn t an option  Tried the preferences mentioned above to no effect   It didn t put the quotes in when there was a CR-LF   Perhaps it only triggers when a comma happens   Copy-paste to Excel is a mainstay of SQL server   Mircosoft either needs a checkbox to revert back to 2008 behavior or they need to enhance the clipboard transfer to Excel such that ONE ROW EQUALS ONE ROW

User · Answer

I ran into the same issue   I was able to get my results to a CSV using the following solution    Execute query Right click in the top left corner of the results grid Select  Save Results as    Choose csv and viola

User · Answer

In order to be able to copy and paste results from SQL Server Management Studio 2012 to Excel or to export to Csv with list separators you must first change the query option   Click on Query then options   Under Results click on the Grid   Check the box next to   Quote strings containing list separators when saving  csv results     This should solve the problem

User · Answer

Seeing as this isn t already mentioned here and it s how I got around the issue     Right click the target database and choose Tasks  gt  Export data and follow that through  One of the destinations on the  Choose a destination  screen is Microsoft Excel and there s a step that will accept your query   It s the SQL Server Import and Export wizard  It s a lot more long-winded than the simple Copy with headers option that I normally use but  save jumping through a lot more hoops  when you have a lot of data to get into excel it s a worthy option

User · Answer

I found a workaround for the problem  instead of copy-pasting by hand  use Excel to connect to your database and import the complete table  Then remove the data you are not interested in   Here are the steps  for Excel 2010    Go to menu Data  gt  Get external data  From other sources  gt  From SQL Server Type the sql server name  and credentials if you don t have Windows authentication on your server  and connect   Select the database and table that contains the data with the newlines and click  Finish    Select the destination worksheet and click  Ok      Excel will now import the complete table with the newlines intact

User · Answer

The best way I ve come up to include the carriage returns line breaks in the result  Copy Copy with Headers Save Results As  for copying to Excel is to add the double quotes in the SELECT  e g     SELECT       ColumnName       AS ColumnName FROM TableName    If the column data itself can contain double quotes  they can be escaped by  double-double quoting     SELECT       REPLACE ColumnName                   AS ColumnName FROM TableName    Empty column data will show up as just 2 double quotes in SQL Management Studio  but copying to Excel will result in an empty cell  NULL values will be kept  but that can be changed by using CONCAT      ColumnName       or COALESCE ColumnName        As commented by  JohnLBevan  escaping column data can also be done using the built-in function QUOTENAME    SELECT QUOTENAME ColumnName       AS ColumnName FROM TableName

User · Answer

Instead of copying  amp  pasting into excel you could export to Excel  Right click the database -  Tasks -  Export Data        Source  SQL Server Native Client  Destination  Excel  Specify Table Copy or Query  pick query and enter your query   CR LF retained in the data   BONUS nulls are not copied as  NULL

User · Answer

Line Split Issues when Copying Data from SQL Server to Excel  see below example and try using replace some characters   SELECT replace replace CountyCode  char 10        char 13        FROM  MSSQLTipsDemo   dbo   CountryInfo

User · Answer

You could try save the query results as excel  change the file extension to  txt  Open using excel  open with     then use text to columns  formatting as text    Not sure if this will work for this situation  but works well for other formatting issues that excel auto-strips off

[sql] New Line Issue when copying data from SQL Server 2012 to Excel

Examples related to sql

Examples related to sql-server-2008

Examples related to excel

Examples related to copy-paste

Examples related to sql-server-2012