Post reply

Can DatabaseMail attach a formatted Excel file?

  • February 29, 2008 at 7:24 am

    #187142

    I have the following SP that will email me an attachment of the results of a specific query:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'PROFILE',

    @recipients = 'MY@EMAILADDRESS',

    @query = 'EXEC USP_SP',

    @subject = 'DATA',

    @attach_query_result_as_file = 1,

    @query_result_separator ='' ,

    @query_result_no_padding = 1,

    @query_result_header =0,

    @query_attachment_filename = 'ACCESS.XLS';

    When I open the attachment obviously each data element is separated by a space but all elements are in 1 column. Is there a way to separate each element into its own column?

    Thanks in advance for your help.

    Adam

  • February 29, 2008 at 2:51 pm

    #784061

    Have you tried playing with the @query_result_separator? Perhaps make it a space or comma instead of an empty string?

    The Redneck DBA

  • February 29, 2008 at 2:52 pm

    #784062

    Because you are naming the file with an "XLS" extension, Excel thinks it is a native file and is not looking for a delimiter.

    I would use "," for a delimiter and use a "CSV" extension on the file, then Excel will recognize it as a delimited file and because it is a csv, should open it in the correct manner.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 29, 2008 at 3:00 pm

    #784066

    Jack Corbett (2/29/2008)

    Because you are naming the file with an "XLS" extension, Excel thinks it is a native file and is not looking for a delimiter.

    I would use "," for a delimiter and use a "CSV" extension on the file, then Excel will recognize it as a delimited file and because it is a csv, should open it in the correct manner.

    Thanks Jack. I did what you said and the file is delimited by commas but still all in the same column. I can open it in Excel but I have to go through the process of telling Excel to separate the comma delimited values.

    I'd really like this to be good to go when you open the attachment file.

    Thanks, Adam.

  • February 29, 2008 at 3:01 pm

    #784067

    Jason Shadonix (2/29/2008)

    Have you tried playing with the @query_result_separator? Perhaps make it a space or comma instead of an empty string?

    I have, and the data rows are still squashed into one column but delimited by a space or other characters (I tried using a "|" but no good either).

    Thanks,

    Adam.

  • May 27, 2008 at 12:53 pm

    #820778

    I have had similar problems with attaching an Excel (*.cvs) file and having the columns separated correctly.

    We have received a solution that works for us. Use @query_result_separator = ' ' (this is a TAB between the ' ' )). It has worked for all of the problematic emails that we have had.

    I hope it works for you as well.

    thanks,

    Ted

  • May 27, 2008 at 1:10 pm

    #820783

    great! the tab between the ' ' works perfectly.

    I had long since given up on this thread. Thanks for the solution.

    Adam.

    Ted Walsh (5/27/2008)

    I have had similar problems with attaching an Excel (*.cvs) file and having the columns separated correctly.

    We have received a solution that works for us. Use @query_result_separator = ' ' (this is a TAB between the ' ' )). It has worked for all of the problematic emails that we have had.

    I hope it works for you as well.

    thanks,

    Ted

  • August 1, 2008 at 8:06 am

    #851899

    This worked for me too!

  • December 17, 2008 at 1:15 pm

    #914128

    Me too, thanks!

  • May 6, 2009 at 7:24 am

    #989510

    TAB for csv worked perfectly. Thanks!

  • December 1, 2009 at 9:30 am

    #1085906

    Hi

    As per above suggestions, I tried to use tab as a query separator but still all the query data is coming in the same column..Is there something that I doing wrong??

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'cv@xx.co.uk',

    @body= 'Please find attached',

    @subject = 'Test Email from SQL Server',

    @profile_name ='DBMailProfile' ,

    @query = 'exec uat.dbo.csp_Report 1,1',

    @attach_query_result_as_file = 1 ,

    @query_result_separator = ' ',

    @query_result_no_padding = 1,

    @query_result_header =0,

    @query_attachment_filename = 'report.csv';

    Im quite stuck here as all query data is coming as one row without column separation

    Thanks

  • December 2, 2009 at 10:59 am

    #1086679

    charu.verma (12/1/2009)

    Hi

    As per above suggestions, I tried to use tab as a query separator but still all the query data is coming in the same column..Is there something that I doing wrong??

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=N'cv@xx.co.uk',

    @body= 'Please find attached',

    @subject = 'Test Email from SQL Server',

    @profile_name ='DBMailProfile' ,

    @query = 'exec uat.dbo.csp_Report 1,1',

    @attach_query_result_as_file = 1 ,

    @query_result_separator = ' ',

    @query_result_no_padding = 1,

    @query_result_header =0,

    @query_attachment_filename = 'report.csv';

    Im quite stuck here as all query data is coming as one row without column separation

    Thanks

    Did you make sure that there is a tab space between the quotes?

    When I do it, there is a marked space like ' '

  • December 3, 2009 at 5:16 am

    #1086995

    Its a 100% tab & not a spcae between quotes..still all data is coming in one column separated by a space,whereas I want each column of the result query to come in a separate column of the excel

  • December 4, 2009 at 11:16 am

    #1087914

    Maybe this will work?

    @query_result_separator = char(9)

    "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
    "Robert A. Heinlein"

  • June 16, 2010 at 1:50 pm

    #1181269

    Thanks a lot, TAB resolved my issue!

Viewing 15 posts - 1 through 15 (of 24 total)

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