email csv attachment using sp_send_dbmail formatted for Excel

Sometimes you want to email the result of a query as an attachment and you would like it to open nicely in excel. By default the attachment will look horrific in excel but you can force it to look nice (1 field from result set mapping to 1 column in excel) by adding the following line to the top of your attachment

“sep=,”

To automatically do this use the following as a template when using sp_send_dbmail . Substitute in your column names and table name where appropiate

SET @Column1Name = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘Column1]’
SET @Query = ‘SELECT Column1 AS ‘ + @Column1Name + ‘, Column2, Column3 FROM myTable’

Youll also want to make sure that you use the following parameters modified to your needs

,@query=@Query
,@attach_query_result_as_file=1
,@query_attachment_filename=’QueryResults.csv’
,@query_result_separator=’,’ –enforce csv
,@query_result_no_padding=1 –trim
,@query_result_width=32767 –stop wordwrap

SOURCE: http://www.purplefrogsystems.com/blog/2014/04/excel-doesnt-open-csv-files-correctly-from-sp_send_dbmail/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: