CODEWITHSUNDEEP
sqlexcelsql-server-2008
sornamins
sornamins

Reputation: 45

Paste SQL Query output with line breaks into single Excel cell

I'm trying to write SQL code that pulls data from more than one field and displays it in a single field, but on multiple lines. The goal is outputting to Excel retaining multiple lines in a single cell. Our current procedure is just using Excel to concatenate two fields into one cell with carriage returns, but I would like to have SQL do that if possible.

For example:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13)  + 'This is line 2.'
print @text

Displays this:

This is line 1.   
This is line 2.

Changing to a Select:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
select @text

Displays this:

This is line 1. This is line 2.

I want to then copy and paste that output into Excel and have the data appear in a single cell so this is all in one cell:

This is line 1.   
This is line 2.

Upvotes: 3

Views: 6909

Answers (3)

pghcpa
pghcpa

Reputation: 843

I found the @tysonwright answer above to be clever and helpful, but for me, CHAR(10) caused two "line breaks" (ALT-Enter) to appear in the wrapped cell even when I pasted values.

So, I solved it as follows:

SQL to replace CRLF with a pipe (or any unique character of your choice) and remove any duplicates:

 SELECT replace(notes2,'||','|') as Notes2 from
    (
    SELECT replace(notes,char(13)+char(10),'|') as Notes2
    FROM   mytable
    ) z

Then in EXCEL, find-replace the pipe "|" to a line break entered as CTRL-SHIFT-J per How to Replace Text with NewLine

This made the wrapped text look as if it was entered with newlines (ALT-Enter) that I could not accomplish with any combination of char(13), char(10).

It probably can be done in VBA or formula with Replace/Subsitute using vblf, but I didn't try it.

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415931

You see the line break in the first snippet because you're looking at the Messages window in Management Studio. There were no results from that code. What you see in the 2nd snippet is how Management Studio shows you results. Think of it like a debugging view of your data. Management Studio is not a reporting tool. The char(13) value will be there in the raw data; it's just not shown to you in this view.

However, three issues remain.

The first issue is how you get the data to Excel. If this is a VBA macro or done via a library the creates Excel-native files directly, you're probably fine. Other options all have problems:

  • If you copy/paste, you are copying from that debugging view where the line break is not present.
  • If you're running a program that creates a CSV file or similar, you're relying on Excel's CSV parser, which isn't likely to handle the line break well.
  • If you're using the "Results to Text" or "Results to File" feature in Management Studio, the character is there, but you're also dealing with some extra header and footer text that Excel won't handle automatically (though you could use this as a copy/paste source).

For the second issue, even in some cases where you have the char(13), that's only half of a line break on Windows systems. You also need a additional CHAR(10) for it to show up in some situations, like this:

SET @text = 'This is line 1.' + char(13) + CHAR(10) + 'This is line 2.'

The final issue is whether Excel will honor the line break even when it's really there. Excel cells don't like line breaks. It wants you to have that kind of data in a new row. Open up a new blank worksheet and let me know how much success you have even manually typing two lines of text into the same cell. You may need to explicitly tell Excel to wrap the text, or change the default view.

Upvotes: 1

tysonwright
tysonwright

Reputation: 1525

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.

Upvotes: 2

Related Questions