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

sql sql-server-2008 excel copy-paste sql-server-2012
MrPink picture MrPink · Nov 14, 2012 · Viewed 112.2k times · Source

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

Robert Jeppesen picture Robert Jeppesen · Nov 21, 2012

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

Related questions

Saving results with headers in Sql Server Management Studio

I am using SQL Server Management Studio. I wish to save the results of a query to an excel file. I choose "save as" and then save to CSV file which I can open in excel. All good except I …

Read More
Table in Excel from SQL Server stored procedure with parameter field in workbook

I need to create a dynamic report using Excel 2010 and SQL Server. Going off of previous ones created by others I figured out how to create the data connection and use the stored procedure with a specific parameter in the …

Read More
How to Export data to Excel in SQL Server using SQL Jobs

I need to export the data from a particular table in my database to Excel files (.xls/.xlsx) that will be located into a shared folder into my network. Now the situation is like this - I need to use …

Read More