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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
If you like this article, you might also like Power BI and Excel
Load comments