New Line Issue When Copying Data from SQL Server 2012 to Excel

Copy & Paste Errors When Moving Data From SSMS to Excel

Replace feed and Carriage returns from your dataset before you can paste into Excel, Try something like this on the columns you are having issues and then try to paste it in excel:

SELECT REPLACE(REPLACE(yourcolumnname, CHAR(13),' '), CHAR(10),' ')
FROM table

Paste SQL Query output with line breaks into single Excel cell

In your SQL query, instead of carriage returns, use:

   " & CHAR(10) & "

Have the column start with an equals sign and double quote, and end with double quote. So, the output from SQL would be:

="This is line 1." & CHAR(10) & "This is line 2."

Put this into Excel. This will look like a mess, until you make sure that under Format Cells you've checked 'Wrap Text', then the carriage returns will appear within the cells.

Pasting From SQL Server 2012 to Excel returns multiple rows from one field

As Sekar mentioned above, that works. In addition I have found that by using:
replace(replace(replace([Column],CHAR(9),'',''),CHAR(10),'',''),CHAR(13),'','')

Replaces all of the carriage returns (which is what was causing my issue) with a single space. Thus when I paste into Excel, issue resolved. Originally I had figured out the CHAR(10) and CHAR(13) workaround when I start seeing it again :(

That's when I cam across the CHAR(9) workaround as well as the following information posted the other day on Microsoft Connect

Posted by JayakumarPanasonic on 3/7/2015 at 3:14 AM

IT IS A BUG AND MS IS STILL SILENT.

Yesterday whole day I spent to extract some data form SQL2012, where I faced same issue. Used CHAR(9), CHAR(10), and CHAR(13) to resolve it temporarily. Really surprised, why MS is taking this so lite and not fixed yet. It used to work fine with SQL2008. Give option SQL2012 configurable through: Tools | Options... or provide additional options, while pasting data in Excel.

Avoid new line (br/) when pasting data to MSExcel

Try formatting the string. For example:

SELECT ..., REPLACE([Comment],'<br/>','''<br/>') ... FROM ...

OR

SELECT ..., REPLACE([Comment],'<br/>','--linebreak--') ... FROM ...

line breaks lost in sql server

SSMS replaces linebreaks with spaces in the grid output. If you use Print to print the values (will go to your messages tab) then the carriage returns will be displayed there if they were stored with the data.

Example:

SELECT 'ABC' + CHAR(13) + CHAR(10) + 'DEF'
PRINT 'ABC' + CHAR(13) + CHAR(10) + 'DEF'

The first will display in a single cell in the grid without breaks, the second will print with a break to the messages pane.

A quick and easy way to print the values would be to select into a variable:

DECLARE @x varchar(100);
SELECT @x = 'ABC' + CHAR(13) + CHAR(10) + 'DEF';
PRINT @x;

Copy- paste data from SQL to EXCEL

Try using Import-Export wizard of SSMS. Dump the data of the table/view into an Excel.

---

Related Topics