Search code examples
sqlexport-to-csvsp-send-dbmail

Export SQL result to csv through sp_send_dbmail

I am trying to create a Job that sends SQL result as a CSV attachment via email. I manage to do it through the following code:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'main',  
@recipients = 'test@email.com',  
@body = 'test',
@query = 'SELECT column1, column2, column3 FROM DB.dbo.Table',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Test.csv',
@query_result_header = 1,
@query_result_width = 256,
@query_result_separator = ',',
@exclude_query_output = 1,
@append_query_error = 1,
@query_no_truncate = 0,
@query_result_no_padding = 1,
@subject = 'Test';

However, I have 2 problems:

  • They are all in one column separated by comma instead of 3 columns.
  • There is an extra row after the column header that contains dashes.

Current CSV File:

enter image description here

Desired CSV File:

enter image description here

Any help will be appreciated :)

Thanks!

Solution

  • So, I just found an answer to my own question.

    Apparently, I have to insert a new row in the top of the file containing “sep=,”. This forces Excel to understand that it is a comma delimited file, and ensures that it will open correctly.

    You can alter the name of the first column to include this header text. We simply rename “Column1” to “sep=,{CR}{LF}Column1”. Then when dbmail prints out the column headers in the file, the Column1 name will be split on two lines, preceeded by “sep=,”.

    Excel treats this first row as an instruction and does not display it, just uses it to make sure it formats the data correctly.

    Full Details here: https://www.purplefrogsystems.com/blog/2014/04/excel-doesnt-open-csv-files-correctly-from-sp_send_dbmail/