New Line Issue when copying data from SQL Server 2012 to Excel
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
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)
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 > Get external data: From other sources > 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.
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;