{"id":7997,"date":"2015-06-01T05:55:10","date_gmt":"2015-06-01T05:55:10","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sending-query-results-to-excel-through-e-mail\/"},"modified":"2021-01-15T21:21:05","modified_gmt":"2021-01-15T21:21:05","slug":"send-sql-server-query-results-to-excel","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/send-sql-server-query-results-to-excel\/","title":{"rendered":"Send SQL Server query results to Excel through email"},"content":{"rendered":"<p style=\"background: white;\"><span style=\"font-family: 'Arial',sans-serif; color: #373737;\">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?\u00a0<\/span><span style=\"font-family: 'Arial',sans-serif; color: #373737;\">Of course, you have already figured it out: Excel! But, what is the best way to send SQL Server query results to Excel?<\/span><\/p>\n<p style=\"background: white;\"><span style=\"font-family: 'Arial',sans-serif; color: #373737;\">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.<\/span><\/p>\n<p style=\"background: white;\"><span style=\"font-family: 'Arial',sans-serif; color: #373737;\">There is an interesting solution for this: We need to send, together the data, a few instructions to Excel about our csv file.<\/span><\/p>\n<p style=\"background: white;\"><span style=\"font-family: 'Arial',sans-serif; color: #373737;\">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.<\/span><\/p>\n<p style=\"background: white;\"><span style=\"font-family: 'Arial',sans-serif; color: #373737;\">The instruction we need to add is \u201csep=,\u201d, to ensure that excel will understand the comma field separator.<\/span><\/p>\n<p style=\"background: white;\"><span style=\"font-family: 'Arial',sans-serif; color: #373737;\">Here a small example with a simple query:<\/span><\/p>\n<pre class=\"csharpcode\">declare @qry varchar(8000)\r\ndeclare @column1name varchar(50)\r\n-- Create the column name with the instrucation in a variable\r\nSET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'customerid]'\r\n\r\n-- Create the query, concatenating the column name as an alias\r\nselect @qry='set nocount on;select customerid ' + @column1name + \r\n             ' ,companyname, contactname, country from customers'\r\n\r\n-- Send the e-mail with the query results in attach\r\nexec msdb.dbo.sp_send_dbmail @recipients=\"Your email\",\r\n@query=@qry,\r\n@subject='Client list',\r\n@attach_query_result_as_file = 1,\r\n@query_attachment_filename = 'result.csv',\r\n@query_result_separator=',',@query_result_width =32767,\r\n@query_result_no_padding=1<\/pre>\n<p>Let&#8217;s notice the following details:<\/p>\n<ul>\n<li>&#8220;sep=,&#8221; instruction is created as part of the first field alias. We use CHR(13) + CHR(10) between the instruction and field name.<\/li>\n<li>Set nocount on is usefull, we don&#8217;t need the record count at the bottom of the results.<\/li>\n<li>We need to set @query_result_Width, otherwise the results could be cutted in the file.<\/li>\n<li>You need to configure database mail to use sp_send_dbmail<\/li>\n<\/ul>\n<p>Now Excel can read this file and you can send many query results through e-mail.<\/p>\n<p><em>If you like this article, you might also like<\/em>\u00a0<a href=\"https:\/\/www.red-gate.com\/simple-talk\/blogs\/power-bi-and-excel\/\">Power BI and Excel<\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dennes Torres explains how to send SQL Server query results to Excel. It seems easy, but there is a catch!&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[126380,126379],"coauthors":[6810],"class_list":["post-7997","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-add-query-results-as-an-attachment","tag-export-sql-server-query-to-excel"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7997","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=7997"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7997\/revisions"}],"predecessor-version":[{"id":89529,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/7997\/revisions\/89529"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=7997"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=7997"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=7997"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=7997"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}