Send SQL Server query results to Excel through email

Dennes Torres explains how to send SQL Server query results to Excel. It seems easy, but there is a catch!

A few weeks ago, I received one challenge: Send audit reports (based on SQL Server Audit) as an attachment through e-mail. When sending the results of a query, the easiest format to use is .CSV, no doubt about this. But how the user would read the .CSV file? Of course, you have already figured it out: Excel! But, what is the best way to send SQL Server query results to Excel?

Creating a file from a SQL Server query for Excel is not as easy as you would expect. If you try to create a .CSV using sp_send_dbmail and read the file in Excel, you will be disappointed: Excel will not understand the columns, and 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 to add is “sep=,”, to ensure that excel will understand the comma field separator.

Here a small example with a simple query:

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.

If you like this article, you might also like Power BI and Excel