0845 643 64 63

Excel doesn’t open CSV files correctly from sp_send_dbmail

A nice little nugget today for anyone who uses SQL dbmail to send data in csv attachments.

Sometimes, depending on your language settings, CSV files don’t open correctly in Excel. Excel ignores the ‘,’ and puts all data in column A. You then have to go through the hassle of closing the file and opening it throug the flat file wizard.

 

excelcsv1

There’s a very nice solution to this that I found in this thread. It’s to insert a new row in the top of the file containing “sep=,”. This forces Excel to understand that it is a comma delimited file, and ensures that it will open correctly.

The only problem, how do you insert a header line when using dbmail?…

[Caution – ugly hack alert…]

You can alter the name of the first column to include this header text. We simply rename “Column1” to “sep=,{CR}{LF}Column1”. Then when dbmail prints out the column headers in the file, the Column1 name will be split on two lines, preceeded by “sep=,”.

excelcsv2

Excel treats this first row as an instruction and does not display it, just uses it to make sure it formats the data correctly.

So how do we do this in SQL?

DECLARE @Column1Name VARCHAR(255)
DECLARE @Query VARCHAR(2048)
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'Column1]'
SET @Query = 'SELECT Column1 AS ' + @Column1Name + ', Column2, Column3 FROM myTable'

EXEC msdb.dbo.sp_send_dbmail
@profile_name=XXX
,[Other parameters as required]
,@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

And there you go. It’s a bit of a hack, but works well.

Frog-Blog-Out

70 Responses to Excel doesn’t open CSV files correctly from sp_send_dbmail

  • Excellent solution. Saved me several hours. Thank you.

  • Great workaround !!!! I didn’t have many options to implement a solution because I wasn’t able to use SSRS neither SSIS. Thank you !!!!

  • Excellent solution thanks.

  • Excellent stuff – much appreciated.

  • Thank you! This is great!

  • Big help, thanks….
    But where do the ———, ———–, ————–, in the second row come from as they are not in the data? Or more importantly, how to remove them?

    • Hi Richard
      There’s no option to remove the dashes I’m afraid, you’re stuck with them if you want to include the header column names.
      However I did see a workaround for this, can’t remember where I’m afraid. And that is to turn off headers in the options (@query_result_header=0) and then have the column headers in the first row of results. e.g.
      SELECT ‘Column1Name’, ‘Column2Name’, ‘Column3Name’
      UNION ALL
      SELECT Col1, Col2, Col3 FROM MyTable

      Not an elegant workaround, but as far as I’m aware it’s the only option you’ll have.
      Regards
      Alex

      • Great thanks. Its works like a charm

      • How can we remove the remove the carriage return in the query, to avoid stop the column text going across multiple rows.

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

  • Thanks Richard! This is great!
    The above works, but how do you store it in a variable to send that in @query of sp_send_dbmail.

    Best,
    Ani

  • Thank you so much!
    Excellent solution!

  • I used this solution but I am not getting the expected output. I followed the same steps. Please review below sample code which I used..

    /*

    declare @columnname varchar(255),@query varchar(2000)
    SET @columnname = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘OrderNo]’
    set @query = ‘select OrderNo as ‘ + @columnname + ‘,Amount from tblOrders with(nolock)’

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘DBAdmin’,
    @recipients = ‘ianujsoni@gmail.com’,
    @subject = ‘SQL Mail’,
    @Body = ‘This is the mail generated from SQL’,
    @query= @query,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = ‘Orders.csv’,
    @query_result_separator=’,’,
    @query_result_no_padding=1,
    @query_result_width=32767

    */

    When I open the csv file column width is not properly set.

    Can you please help me to address this issue?

    Thanks,
    Anuj

    • Hi Anuj
      There is nothing in the CSV that tells excel what column width to use, so all column widths are default. This is the expected behaviour and I don’t think it can be overridden, unless there’s a setting in Excel somewhere. It’s certainly not a bug in the CSV generation code.
      The @query_result_width just stops a single row of results being broken up into two rows.
      Regards
      Alex

  • I found the problem is db_mail sends the csv with UNICODE encoding (save as in notepad), once you change the file to ANSI, wala, XL opens it properly.

    • Good info, thanks for sharing Paul

      • Yes, need to figure out a way to have it send csv attachment with ANSI as default, from what I’ve seen, may need to create my own sp_send_dbmail2 with encoding as a @parameter, as there seems to be no configurable option in dbmail, or existing parameter in the sp to indicate so. if I find any “quick” solution, I will post here. Thank god for Google, 20 years ago nothing of the sort, and issues like this took weeks to resolve. 😉

  • Genius.

  • Brilliant! Million thanks!

  • You can make it easier (works in SQL Server 2008 R2):
    SET @Query = ‘print ”sep=,”; SELECT Column1, Column2, Column3 FROM myTable’

  • Setting @query_result_separator to a ‘tab’ delimited column works also. Since sql mail always saves the csv file as unicode, this is the only delimiter that works by default:

    DECLARE @tab char(1) = CHAR(9)

    @query_result_separator=@tab

    • Good info, thanks Rob

    • Rob,
      The tab character solution worked perfectly! Thank you!

      …on a side note: is anyone a little surprised that Msoft didn’t ensure these two pieces worked together properly?

    • Kind of related, but I’ve had problems with stored procedure code saved with “UTF-8” encoding to where they compiled normally (via ps release scripts), but did not run normally. When “re-saved” with Unicode or ANSI encoding and re-compiled, they ran normally. One of our developers was saving with UTF-8 encoding, very strange.

    • It works

    • lifesaver! this worked perfectly as I couldn’t run the select within the job spec (lots of unions and made up field names).

    • This works too

  • fantastic hack! works perfect. Thank you for the post!

  • You sir,

    Are a gentleman and a scholar.

  • I just use a tab for @query_result_separator and it formats correctly for viewing csv in excel.

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name=’donotreply’
    ,@recipients =’xx@xx’
    ,@query= @query
    ,@subject= ‘xx’
    ,@attach_query_result_as_file=1
    ,@query_attachment_filename=’xx.csv’
    ,@query_result_separator=’ ‘ — ***LITERAL TAB****
    ,@query_result_no_padding=1 –trim
    ,@query_result_width=32767 –stop wordwrap

    • It messed up my formatting when I submitted, but it is supposed to be the amount of space for a tab between the two quotes

  • Great Thanks
    I faced this issue from 2 days, but with your help this issue is resolved now. But I have another issue related to File Size. My File size is more than 1 MB then in this case what should I do? Please suggest any one

    • You can change the attachment size limitation in the Database Mail Configuration, just increase the ‘Maximum File Size (bytes)’ property

      • thanks Alex, but I read any where this limit is up to the 1 MB, But I can set this 1000000000 bytes (1000 MB), so what is correct? I am also send to you reference site below.
        https://msdn.microsoft.com/en-IN/library/ms190307.aspx (Under the @file_attachments description).

        • The link you included states correctly that “By default, Database Mail limits file attachments to 1 MB per file”.

          But this is only the default, it can be changed to anything you like, higher or lower.

          Although I would not recommend changing this to 1GB as per your comment – your email server may complain about this!!

  • Hello Alex:

    Thanks for this code, its perfect what I need.

    I appended SET NOCOUNT ON for @Query parameter.

    But I need to remove the —– from the attachment file. Can you please help me?

    • As far as I know, the only way of doing that is by removing the header from the output (@query_result_header=0).
      If you need the header row then just change your query so that the first line of results is the text that you want in the column headers.
      i.e. SELECT ‘ColumnHeader1’, ‘ColumnHeader2’, etc.
      UNION ALL
      SELECT [your original query]

  • I just discovered a great solution for file attachments with SQL sp_send_dbmail Stored procedure

    If we want to send output of any query as excel attachment (Where TABLE1 can be a temp table or permanent table in DB)

    DECLARE @NumberRecords INT
    SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1

    SET @NumberRecords = @@ROWCOUNT
    DECLARE @RowCount INT= 1
    DECLARE @DBDetailTotal VARCHAR(MAX) = ”
    DECLARE @DBDetail VARCHAR(1500)
    Iterate through the table rows and rap every column with html tag

    WHILE @RowCount <= @NumberRecords
    SELECT @DBDetail = '’
    + [COLUMN1] + ”
    + [COLUMN2] + ”
    + [COLUMN3] + ”
    FROM @TABLE1 WHERE ID = @RowCount

    SET @RowCount = @RowCount + 1
    SET @DBDetailTotal = @DBDetailTotal + @DBDetail
    END

    SET @Query= ‘PRINT ”’ + @DBDetailTotal + ””

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘Email Profile Name’
    @recipients = @ToList,
    @copy_recipients = @CCList,
    @subject = @EmailSubject,
    @body = @EmailBodyTemplate,
    @body_format = ‘HTML’,
    @execute_query_database = @DBName,
    @query = @Query,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = ‘AttachedFile.xls’,
    @query_result_header = 1,
    @query_result_separator = ‘,’,
    @exclude_query_output = 0,
    @query_result_no_padding = 1,
    @query_result_width = 32767

    With this approach we can also add formatting in HTML table tag which can be displayed in Excel file also.

    • The code is not posted properly… the goal is to iterate through the rows of table to be send as attachment , wrap the rows in HTML TABLE TR TD attributes and PRINT it in @query parameter

  • Fantastic! One of those rate but awesome” just what the doctor ordered” experiences.

  • Hi,
    I am trying to send the query result as email with attachment. But its not working. Since the Query statement is very long.
    Do we have any limitations on this or am i missing anything?
    Please check the below Syntax.

    IF EXISTS (SELECT content_id, content_title, last_edit_fname,last_edit_lname
    FROM [EktronDev02].[dbo].[content]
    WHERE [content_html] LIKE ‘%staging.hfma.org%’ OR [content_html] LIKE ‘%dev.hfma.org%’)
    BEGIN
    DECLARE @tab char(1) = CHAR(9)
    SET @Query = ‘select i.content_id,i.content_title,j.FolderPath,links.link as Incorrect_URL,k.urlaliasnm as Alias,lib.filename as Direct_URL,i.last_edit_lname,i.last_edit_fname,i.last_edit_date from content as i inner join content_folder_tbl as j on i.folder_id=j.folder_id inner join UrlAliasMapping as k on i.content_id=k.TargetID inner join [library] as lib on i.content_id=lib.content_id CROSS APPLY dbo.GetHFMAContentLinks(cast(i.content_html as nvarchar(max))) links where i.content_type!=3 and (links.link like ‘%www.hfma.org%’ OR links.link like ‘%dev.hfma.org%’ OR links.link like ‘%staging.hfma.org%’) order by content_id ASC’
    EXECUTE msdb.dbo.sp_send_dbmail
    @profile_name = ‘CMS_sql_mail’,
    @recipients = ‘test@gmail.com’,
    @subject = ‘Production Elements Referencing Staging/Dev’,
    @body_format = ‘TEXT’,
    @Body = ‘Attached are those Production Elements Referencing Staging and/or Dev.’,
    @attach_query_result_as_file = 1,
    @query_attachment_filename=’BadReference.csv’,
    @query_result_width = 1200,
    @query_result_header = 0,
    @query_result_separator=@tab,
    @query_result_no_padding=1,
    @query = @Query
    END

    Lemme know if i am missing any.

    Thanks in Advance
    Uma

    • Hi Uma
      The @query parameter is an nvarchar(max), so you shouldn’t have any problems with the length of query you’re using. Are you declaring your local @Query variable as nvarchar(max)? If not then add this declaration and it should hopefully work for you.
      Thanks
      Alex

  • Hi,

    I am trying to send the result of two queries into two different sheets in an attachment .

    Is it possible to do that? If yes, can someone help me with this?

    Thanks
    Lavanya

    • Hi Lavanya
      I’m not aware of any way of doing this with this method of emailing. You’d have to use Reporting Services instead.
      Regards
      Alex

    • Yes, dynamically build the sp_send_dbmail call

      ou have to build @file_attachments.

      DECLARE @attachments varchar(512)
      SET @s = @2PATH + ‘;’ + @1PATH
      Then pass @attachments to @file_attachments

  • Hi, I have been struggling for the above task since two days
    I want following format need to do. So please help me
    i tried a lot to get that format.
    declare @qry varchar(8000)
    declare @column1name varchar(50)
    SET @Column1Name = ‘[sep=;’ + CHAR(13) + CHAR(10) + ‘StatusTypeID]’
    select @qry=’set nocount on;select top 1 TypeID ‘ + @column1name +’, col2,col3,col4
    exec msdb.dbo.sp_send_dbmail
    @recipients=”mailID”,
    @query=@qry,
    @subject=’Test Email’,
    @body = ‘This is just for testing ‘,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = ‘QueryResults.csv’,
    @query_result_separator=’;’,@query_result_width =32767,
    @query_result_no_padding=1

    TypeID Detail message AID Stime Etime

    I want those columns result in each column separately and should be wrapped.Because, in the above columns Detail and message having more text with include comma(,) and Stime and Etime should be come as dd/mm/yyyy time.

  • Hi, I have been struggling for the above task since two days
    I want following format need to do. So please help me
    i tried a lot to get that format.
    declare @qry varchar(8000)
    declare @column1name varchar(50)
    SET @Column1Name = ‘[sep=;’ + CHAR(13) + CHAR(10) + ‘StatusTypeID]’
    select @qry=’set nocount on;select top 1 TypeID ‘ + @column1name +’, col2,col3,col4
    exec msdb.dbo.sp_send_dbmail
    @recipients=”mailID”,
    @query=@qry,
    @subject=’Test Email’,
    @body = ‘This is just for testing ‘,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = ‘QueryResults.csv’,
    @query_result_separator=’;’,@query_result_width =1000,
    @query_result_no_padding=1

    TypeID Detail message AID Stime Etime

    I want those columns result in each column separately and should be wrapped.Because, in the above columns Detail and message having more text with include comma(,) and Stime and Etime should be come as dd/mm/yyyy time.

    • Can you give me some indication of what isn’t working? Or what error you’re getting?

    • Use Quotes around data so as it does not think embedded commas in field is not new columns. Or use a different column delimiter, such as tab or pound (#).

  • i noticed that if you define tab as a separator then .csv file opens up very nicely in both xls and xlsx format.

  • Hi Alex,

    My query is doing well with attachment the problem is that it is not including all the records from result set. There are more than 2700 records the same query returns from table but when I check in mail, the attachment has only 150 records everytime I send the mail. Do you have any idea?

    Query :

    set @query = ‘select COL1, COL2,COL3,COL4,COL5 fromMyDB..MyTable with(nolock)’

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @profile,
    @recipients = @recipient,
    @subject = @subject,
    @Body = @body,
    @query= @query,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = ‘Myfile.xlsx’,
    @query_result_separator=’,’,
    @query_result_no_padding=1,
    @query_result_width=32767

    I have tried .csv, .xls, .xlsx formats..

    • This behaviour is a bug in the code which limits the total size of the attachment. This has been resolved in some versions of SQL Server. There’s a Connect item that contains some discussion and links that may be useful.
      http://connect.microsoft.com/SQLServer/feedbackdetail/view/850260/sql-2014-sp-send-dbmail-64kb-query-result-limit

  • Alex, have you figured out had to get a column to export leading zero’s with your attached code? Your solution worked great and exported into the correct .csv format, but one of my columns has a value of 0001 which is reduced to 1 once you open the excel.

    Do you know a work around for this? I have tried to add a single quote before the 0001 so that it’s formatted like ‘0001 but it still didn’t seem to work.

    Let me know if you have any ideas! Great post!

  • Great thread, thanks all!

  • Hi Alex I found same problem every column data was placed in one column in CSV file so please help me . where I can placed my csv formet code in stored procedure

    Regards
    vikash

  • How we can remove the number of rows affected the CSV is showing at the end of file. Please help.

  • Hi Alex,
    It’s great solution, however I’m not able to use this trick together with workaround removing dashes from result file.
    Here is my query:

    DECLARE @Column1Name VARCHAR(255)
    DECLARE @Qry2 VARCHAR(2048)
    SET @Column1Name = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘name]’
    SET @Qry2 = ‘SELECT ”name” AS ‘ + @Column1Name + ‘, ”ip”, ”model” union all select name, ip, model from myTable’

    exec msdb.dbo.sp_send_dbmail
    @recipients=’mail@gsk.com’,
    @query=@qry2,
    @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,
    @query_result_header=0

    Despite adding sep alias I’m still getting results with comas. Any idea how to fix that?

  • Hi Alex,
    I’m having the same issue as Woj. I can’t seem to combine the UNION column headers and get the file to open in excel in columns without the dashes.

    • I don’t believe there is any way of removing the dashes, at least when using this technique.

      • EXEC msdb.dbo.sp_send_dbmail
        @profile_name = ‘maheshnew’,
        @recipients = @email,
        @body=@description,
        @subject = @body11,
        — @query = ‘SET NOCOUNT ON;select * from ##temp;SET NOCOUNT OFF;’,
        @query=’SET NOCOUNT ON;select ”Vouchercode” as VoucherCode,”Expirydate” as ExpiryDate
        union all select Vouchercode,convert(varchar,expirydate) FROM ##temp;SET NOCOUNT OFF;’,

        @attach_query_result_as_file = 1,
        @query_attachment_filename= ‘Vouchers.xls’,

        @query_result_separator=@tab,
        @query_result_no_padding=1,
        @query_result_header = 0,
        @exclude_query_output =1,
        @append_query_error=0,
        @query_result_width = 1000

  • This code is removes the dashes and no of rows effected on excel sheet.

  • am getting “Sep” keyword in first row. How to fix this?

  • Thanx a lott for your post. It helped. Cheers!!!

  • Hi Alex, that’s a great post, thank you. I also would like to share what worked for me, with less effort. It might help the others too.

    DECLARE @query_result_separator CHAR(09)
    , @query_result_width SMALLINT

    –// Here is the trick
    –// Just add this value in the procedure params and it should work fine.
    SET @query_result_separator = CHAR(9)
    SET @query_result_width = 32767

    EXEC msdb.dbo.sp_send_dbmail
    @recipients = @EmailAddress
    , @copy_recipients = @pEmailCopy_recipients
    , @subject = @EmailSubject
    , @body = @EmailBody
    , @query_attachment_filename = @FileName
    , @attach_query_result_as_file = 1
    , @query_result_header = 1
    , @query_result_width = @query_result_width
    , @append_query_error = 1
    , @query_result_no_padding = 1
    , @query_result_separator = @query_result_separator
    , @query = @SQLStmt

  • There are 2 Simpler Methods:

    1)
    Default Text File Sep for Excel is tab (even if extension is csv).
    @query_result_separator=’ ‘

    2)
    Because DBMail just exports the console output you can add a line using Print
    ,@query = ‘SET NOCOUNT ON
    GO
    Print ”Sep=,”
    SELECT * FROM table’
    ,@query_result_separator =’,’

  • Thanks for the post.

  • Thank you so much.

  • Nice Post

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags are not allowed.

364,795 Spambots Blocked by Simple Comments

The Frog Blog

I'm Alex Whittles.

I specialise in designing and implementing SQL Server business intelligence solutions, and this is my blog! Just a collection of thoughts, techniques and ramblings on SQL Server, Cubes, Data Warehouses, MDX, DAX and whatever else comes to mind.

Data Platform MVP

Frog Blog Out
twitter
rssicon