Adding alt+enter (excel linefeed) in tsql code

Question

I'm trying to create a script to return concat lines from the DB and with c# populate an excel file with the text.

I've managed every thing, just can't find a solution for the line feed...

Tried char(10)+char(13) and many other.

I know that in excel if one want to add new line, he show use the alt+enter. is there are code of that to add to my tsql script to get the linefeed in the excel file?

Thank you, Erez

Answer 1

I had the same problems, but mine is a copy-and-paste from the SQL Server Management Studio results window into an Excel file. It's the same issue, just more direct.

For those folks that don't understand the problem, here's a repro. Run the following in SSMS and copy-and-paste the results into Excel. Set the cell to wrap text.

SELECT 'Hello' + CHAR(10) + 'World'

You'll see Hello on one cell and World on the cell below it. What we're trying to see is

Hello
World

in one cell with a line break.

My solution was to surround the result in quotes. There are several ways to do this. I found this to look the cleanest:

SELECT QUOTENAME('Hello' + CHAR(10) + 'World', '"')

The result is "Hello World" in the SSMS results window. But when pasted into Excel and the cell set with wrap text, I would get the desired result.