Decimal seperator Excel format

  • Hi all,

    I'm trying to send a sql statement using sp_send_dbmail procedure. Result should be attached as an excel file. Unfortunately the format of decimal numbers do not fit to German format. So German Excel does not recognize this as a figure but rather as text 🙁

    Does anyone has an idea how to handle this?

    declare @sql varchar(1000)

    set @sql='select top 10 SubTotal from AdventureWorks.Sales.SalesOrderHeader'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'my_profile',

    @recipients = 'my_recipient',

    @body='testbody',

    @subject = 'testsubject',

    @query = @sql,

    @attach_query_result_as_file=1,

    @query_result_separator='',

    @query_result_header=1,

    @query_attachment_filename='result.xls'

    Result should be like:

    SubTotal

    24643,9362

    1553,1035

    But in Excel I find:

    SubTotal

    246.439.362

    15.531.035

    Thanks in advance

    smerg

  • Its not an Excel file, so you can't do excel specific formatting. You are essentially creating a csv file and calling it .xls which Excel automatically opens and applys local formatting to.

    So you I would suggest a couple options.

    1. Look at formattig the data in the select so that when it comes in its formatted properly.

    2. Use SSIS or another mehtod to create an actual real XLS file and not a csv.

    3. Do post formatting once in Excel with a macro.

    Hope it helps.

  • Hi dmc,

    thanks for your answer. I created a SSIS but this causes some overhead. I hoped to get a solution as general and as easy like sp_send_dbmail.

    Best Regards

    Smerg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply