CODEWITHSUNDEEP
sql-serverdbmail
user4778
user4778

Reputation: 53

Database mail messed up CSV attachment

Is there any way to format output CSV from below statement to be easily readable by Excel? As for now it opens all columns messed up into one column. I have tried already different deperators.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'auto-reporting'
  , @recipients = @recipientsList   
  , @subject = 'MC Auto Reports'
  , @query = 'select * from test.dbo.temp'
  , @attach_query_result_as_file = 1
  , @query_result_separator =','
  , @query_result_no_padding = 1
  , @query_attachment_filename = @FileName;

Upvotes: 0

Views: 2680

Answers (2)

Nick.Mc
Nick.Mc

Reputation: 19235

Try changing

, @query_result_separator =','

to

, @query_result_separator = @separator

Just add this code before sending the email

DECLARE @separator CHAR(1)
SET @separator = CHAR(9)

This makes the seperator TAB which Excel likes better.

Upvotes: 1

John Doe
John Doe

Reputation: 11

Try to edit produced .csv file in notepad. In the very first line type [sep=,] without quotes and save it. Then try to open it in Excel if its correctly displayed then you need to check your regional settings in windows.

Upvotes: 1

Related Questions