New Line Issue when copying data from SQL Server 2

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.

Answer 1

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)

1. Go to menu Data > Get external data: From other sources > From SQL Server
2. Type the sql server name (and credentials if you don't have Windows authentication on your server) and connect.
3. Select the database and table that contains the data with the newlines and click 'Finish'.
4. Select the destination worksheet and click 'Ok'.

Excel will now import the complete table with the newlines intact.

Answer 2

I ran into the same issue. I was able to get my results to a CSV using the following solution:

1. Execute query
2. Right click in the top left corner of the results grid
3. Select "Save Results as.."
4. Choose csv and viola!

Answer 3

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

Answer 4

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)

Answer 5

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;

Answer 6

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!