DB Mail Export to Excel some row wrap to new row

The problem is not SQL Server, it's Excel and the CSV format. You have a line break as content in one cell. And Excel is not good at handling those.

Possible fixes:
1) strip line breaks in the query using REPLACE().
2) read the file by using the Excel external data import.

ASKER

I don't believe the issue is Excel, as I stated in my original post if I run the VIEW in a query window then copy with headers and past into Excel it does not wrap.
I tried exporting as xlsx but when opening the file in Excel it indicates that the file format or extension is invalid.

1) I did use REPLACE in the view used by the dynamic SQL "REPLACE(REPLACE(CAST(rma.problem_description AS VARCHAR(MAX)),CHAR(10), ''), CHAR(13), '')AS problem_description" to remove the CR LF

2) This needs to be automated hence the use of DB mail

suggest you attach to post example of the csv file which is doing it - have you examined that to see where it is splitting?

ASKER

I cant attach an example due to the sensitivity of the data. The weird thing is that if I run the view in a query window, copy with headers and paste it into the spread sheet it does not wrap those rows, it only does that when I send it as a DBmail .CSV attachment

Craft a repro showing that behavior.

btw, this can already help you to understand the problem yourself.

ASKER

is there anything in Excel that will let me see formatting like in word

Don't use Excel. Use a text editor like Notepad++ or sublime.

ASKER

Ok, I pasted the CSV content into Note++ and there is a CRLF where it wraps even though I used the replace function.

What column? Seems that your REPLACE() does not cover this.

ASKER

The problem_description column I use the replace function in the View
REPLACE(REPLACE(CAST(rma.problem_description AS VARCHAR(MAX)),CHAR(10), ''), CHAR(13), '')AS problem_description to remove the CR LF but it still wraps.

and I have even used it in the dynamic SQL
DECLARE
@tab char(1) = CHAR(9)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Tri Company DB Mail',
@recipients = 'xxxx@xxxxx.com',
@query = 'SET NOCOUNT ON
SELECT ra_created, return_number, model_no, item_number, serial_number, REPLACE(REPLACE(CAST(problem_description AS VARCHAR(MAX)),CHAR(10), ''''), CHAR(13), '''')AS problem_description
FROM ATI.[dbo].[SSG_RA_DATA]' ,
@subject = 'RA results',
@attach_query_result_as_file = 1,
@query_attachment_filename='RATEST.CSV',
@query_result_separator=@tab,
@query_result_no_padding=1

I copied one of the rows that wrap from Excel and pasted it into Notepad++ and as you can see there are CRLF codes, so it is not stripping them out.

Please try a simpler sample like:

DECLARE @Sample TABLE
    (
      ID INT ,
      ContentText VARCHAR(255)
    );

INSERT  INTO @Sample
VALUES  ( 1, 'Some Text.' ),
        ( 2, 'Some more 
Text' );

SELECT  * ,
        REPLACE(S.ContentText, CHAR(13) + CHAR(10), '') AS SanitizedContentText
FROM    @Sample S;

Open in new window

ASKER

Nope... Still didn't work. If I run the VIEW in a query window then copy with headers and past into Excel it does not wrap.

ASKER

OK... so I ran the Dynamic SQL as a standalone copied with headers and pasted into Excel it does not wrap.
DECLARE @query NVARCHAR (4000)
SET @query = 'SET NOCOUNT ON
SELECT ra_created, return_number, model_no, item_number, serial_number, REPLACE(problem_description, CHAR(13) + CHAR(10), '''') AS problem_description
FROM ATI.[dbo].[SSG_RA_DATA]'
EXECUTE sp_executesql @query
So... I would guess it is how SQL is exporting it to the CSV file

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

2008R2

Have you tested my short sample?

ASKER

The thing is that not every line wraps

hmm, try adding an explicit HTML body as in my sample.

is it actually a CR followed by LF or could it just be CR or just LF. In your earlier examples you were replacing CR then LF with blank but above only specifically CR LF.

How about something like this as part of what you have above to replace and highlight where there are CR or LF in there

Steve

21: REPLACE(REPLACE(S.ContentText, CHAR(13) , '*CR*'),CHAR(10) , '*LF*') AS SanitizedContentText

ASKER

Steve,
Added your suggestion and it still wraps the same rows. The weird thing is if I just run the query and copy with headers then Paste into Excel no problem, it is only with the DB Mail export to excel that it wraps

So does it show any *CR* or *LF* in the text though which is what I was suggesting replacing with?

hmm, the only thing I can imagine, is that for some reason your e-mail gets an artificial line break, cause there was once on defined (72 chars per line or so)..

ASKER

Steve, No...

Odd that it doesn't always do it. Is it at certain character width etc. i.e. do you need to use for your server:

@query_result_width = 1000

or something

Steve

ASKER

Steve... I was looking for the *CR**LF* in the rows that were wrapping in the export from the DB mail but they did show up in other rows that weren't wrapping.

Ok so CR LF Isn't in your data then, they ARE being replaced properly with blanks normally (or *CR**LF* above) so it is added elsewhere. Possibly result-width?

ASKER

OK... where would I increase the results width

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Steve I am Splitting the Points as ste5an has been helping from the beginning and I used his format to clean up my code.