ms sql database mail and csv file attachments - how to "escape" correctly

How do I ensure that query results placed in a csv file will "escape" correctly? Below works ok, but if there is a comma in the data, the csv file does not show correctly in excel. Is there some way to contain each cell's data in the csv so it will open correctly in excel?

Thanks.


DECLARE @sub VARCHAR(100)
DECLARE @qry VARCHAR(1000)
DECLARE @msg VARCHAR(250)
DECLARE @query NVARCHAR(1000)
DECLARE @query_attachment_filename NVARCHAR(520)

SELECT @sub = 'Nightly Report'
SELECT @msg = 'This is an automated message.'
SELECT @query = 'SELECT * FROM [sem].[dbo].[04AGSEMQuoteFormProduction] WHERE [sem].[dbo].[04AGSEMQuoteFormProduction].[ServerTime] >= cast(getdate() as date)'

SELECT @query_attachment_filename = 'report.csv'

EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'AG Reporting',
            @recipients = 'email@email.com',
            @body = @msg,
            @subject = @sub,
            @query = @query,
            @query_attachment_filename = @query_attachment_filename,
            @attach_query_result_as_file = 1,
            @query_result_header = 1,
            @query_result_width = 256 ,
            @query_result_separator = '	' ,
            @query_result_no_padding =1;

Open in new window

Mark BDirector ITAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chaauConnect With a Mentor Commented:
You have two options:
1. Remove all commas in the string values (using REPLACE(column1, ',', '')
2. Enclose each column in double quote ('"' + column1 + '"')

So, your query string will look like this:

SELECT @query = 'SELECT columnInt1, '"' + columnStr1 + '"', columnDate1, columnInt2, '"' + columnStr2 + '"', etc FROM [sem].[dbo].[04AGSEMQuoteFormProduction] WHERE [sem].[dbo].[04AGSEMQuoteFormProduction].[ServerTime] >= cast(getdate() as date)'

Open in new window


Read about CSV file rules in Wikipedia
0
 
Mark BDirector ITAuthor Commented:
Thanks, #2 worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.