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.



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=,”.


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)
SET @Column1Name = '[sep=,' + CHAR(13) + CHAR(10) + 'Column1]'
SET @Query = 'SELECT Column1 AS ' + @Column1Name + ', Column2, Column3 FROM myTable'

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


84 comments on “Excel doesn’t open CSV files correctly from sp_send_dbmail

  1. A Fan on said:

    Excellent solution. Saved me several hours. Thank you.

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

  3. Janardhan on said:

    Excellent solution thanks.

  4. Mez Pattni on said:

    Excellent stuff – much appreciated.

  5. Thank you! This is great!

  6. Richard on said:

    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’
      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.

      • Palak Patel on said:

        Great thanks. Its works like a charm

      • Venkatesh on said:

        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’

  7. 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.


  8. Katy on said:

    Thank you so much!
    Excellent solution!

  9. Anuj on said:

    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’,


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

    Can you please help me to address this issue?


    • Alex on said:

      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.

  10. Paul on said:

    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.

    • Alex on said:

      Good info, thanks for sharing Paul

      • Paul on said:

        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. 😉

  11. nick on said:


  12. Nick Khor on said:

    Brilliant! Million thanks!

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

  14. 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)


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

  16. Mieke on said:

    You sir,

    Are a gentleman and a scholar.

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

    EXEC msdb.dbo.sp_send_dbmail
    ,@recipients =’xx@xx’
    ,@query= @query
    ,@subject= ‘xx’
    ,@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

  18. Happy Rawat on said:

    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

      • Happy Rawat on said:

        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!!

  19. Vaishali on said:

    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.
      SELECT [your original query]

  20. Vaishali on said:

    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

    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

    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.

    • Vaishali on said:

      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

  21. Bill Moradkhan on said:

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

  22. Uma on said:

    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%’)
    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_result_width = 1200,
    @query_result_header = 0,
    @query = @Query

    Lemme know if i am missing any.

    Thanks in Advance

    • Alex on said:

      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.

  23. Lavanya on said:


    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?


    • Alex on said:

      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.

    • Pete on said:

      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

  24. Chandra Sekhar on said:

    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
    @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,

    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.

  25. Chandra Sekhar on said:

    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
    @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,

    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.

    • Alex on said:

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

    • Pete on said:

      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 (#).

  26. Anuj on said:

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

    • Avinash Jadhav on said:

      hi… can u pls give me an example with putting exact value for @query_result_separator so that we will get an excel sheet in desired format. I am also facing the same issue.

  27. Jignesh on said:

    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’,

    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.

  28. Bryan on said:

    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!

  29. Great thread, thanks all!

  30. vikash on said:

    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


  31. mangesh khandave on said:

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

  32. 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
    @subject=’Client list’,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = ‘result.csv’,
    @query_result_width =32767,

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

  33. 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.

      • Mahesh Embadi on said:

        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = ‘maheshnew’,
        @recipients = @email,
        @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_header = 0,
        @exclude_query_output =1,
        @query_result_width = 1000

  34. Mahesh Embadi on said:

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

  35. prasath on said:

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

  36. laksh on said:

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

  37. Everton on said:

    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

  38. Parax on said:

    There are 2 Simpler Methods:

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

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

  39. Taha on said:

    Thanks for the post.

  40. Thank you so much.

  41. kashyap nalapatla on said:

    Nice Post

  42. Michael G. on said:

    Thanks , It helps , But still looking to remove second row lines.

  43. Oumaima haiki on said:

    Saved my life thank you

  44. Ritesh Singh on said:

    Hi Alex,
    Can be add formatting like making headers bold, adding Borders in the resulted excel file.
    Can we also add one more result sheet in the same resulted excel.
    “The File Format and extension of result.xls don’t match” error is coming after opening the file.

    • Alex Whittles on said:

      Hi Ritesh
      I’m not aware of any way of formatting data, as the export is a csv text file, not an actual Excel formatted file. This is also the reason why you can’t have multiple results. To do this you’d need to use SSRS I expect.
      Your error is happening because the file is a csv file, but you’ve given it a .xls extension. It is not an Excel file, so you should give it a .csv extension instead.

  45. I had this problem. This is wonderful article which fixed my problem.
    Thank you.

  46. Amazing workaround, I was in need of this Workaround Badly

    Thank you

  47. Hemal on said:

    Its working perfectly ,Thanks

  48. Thanks!

  49. Ghost on said:

    Thank you very much man!!!!

  50. Bruce on said:

    Still a valuable post after 7 years! Thanks.

Leave a Reply

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


371,433 Spambots Blocked by Simple Comments

HTML tags are not allowed.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.


Alex Whittles
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out