Sending query results to Excel through e-mail

The easiest format to use is .CSV, no doubt about this. But how the user would read the .CSV file ?

Of course you already thinked: Excel !

That’s not so easy as we would expect. If you try to create a .CSV using sp_send_dbmail and read in excel you will be disapointed: Excel will not understand the columns, the data will be a mess.

There is an interesting solution for this: We need to send, together the data, a few instructions to excel about our csv file.

To send this instruction we need to change our query: The instruction needs to be on top of the file, so we need to create an alias for the first field in our query result, concatenating the instruction with the field name.

The instruction we need do add is “sep=,”, to ensure that excel will understand comma field separator.

Here a small example with a simple query:

declare @qry varchar(8000) declare @column1name varchar(50) -- Create the column name with the instrucation in a variable SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'customerid]'   -- Create the query, concatenating the column name as an alias select @qry='set nocount on;select customerid ' + @column1name +              ' ,companyname, contactname, country from customers'   -- Send the e-mail with the query results in attach exec msdb.dbo.sp_send_dbmail @recipients="Your email", @query=@qry, @subject='Client list', @attach_query_result_as_file = 1, @query_attachment_filename = 'result.csv', @query_result_separator=',',@query_result_width =32767, @query_result_no_padding=1

Let’s notice the following details:

• “sep=,” instruction is created as part of the first field alias. We use CHR(13) + CHR(10) between the instruction and field name.
• Set nocount on is usefull, we don’t need the record count at the bottom of the results.
• We need to set @query_result_Width, otherwise the results could be cutted in the file.
• You need to configure database mail to use sp_send_dbmail

Now Excel can read this file and you can send many query results through e-mail.