Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Can DatabaseMail attach a formatted Excel file? Expand / Collapse
Author
Message
Posted Friday, February 29, 2008 7:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:20 PM
Points: 42, Visits: 117
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
Post #462348
Posted Friday, February 29, 2008 2:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, December 15, 2014 10:28 AM
Points: 1,544, Visits: 2,274
Have you tried playing with the @query_result_separator? Perhaps make it a space or comma instead of an empty string?

The Redneck DBA
Post #462701
Posted Friday, February 29, 2008 2:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:26 AM
Points: 10,381, Visits: 13,436
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #462702
Posted Friday, February 29, 2008 3:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:20 PM
Points: 42, Visits: 117
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.
Post #462706
Posted Friday, February 29, 2008 3:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:20 PM
Points: 42, Visits: 117
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.
Post #462707
Posted Tuesday, May 27, 2008 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 19, 2008 2:40 PM
Points: 2, Visits: 29
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
Post #507195
Posted Tuesday, May 27, 2008 1:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:20 PM
Points: 42, Visits: 117
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
Post #507205
Posted Friday, August 1, 2008 8:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 24, 2013 7:44 AM
Points: 1, Visits: 8
This worked for me too!
Post #545154
Posted Wednesday, December 17, 2008 1:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 2, 2009 9:35 AM
Points: 10, Visits: 20
Me too, thanks!


Post #621575
Posted Wednesday, May 6, 2009 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 10, 2010 3:12 AM
Points: 1, Visits: 38
TAB for csv worked perfectly. Thanks!
Post #711047
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse