Write query result to Excel file in different tab?

[qjade]

Hello friends,

I am attempting to write the result of two separate subquerries into two separate tabs of a single excel workbook. I only know of a way to write out via bcp and xp_cmdshell to a tab delimited file.

i.e.

exec master.dbo.xp_cmdshell 'bcp "select top 3* from tblSomeTable" queryout "\\MyMachineNetworkName\C$\SomeOutputFile.txt" -c -T -t,'


My problem is that I do not know how to specify the various sheets (i.e. Subquerry1 --> Sheet1, Subquerry2 --> Sheet2) to send my queries's results to. I want to accomplish this within a single Stored Procedure. However, any previous experience or success story with other methodology, such as Crystal Report, is more than welcome.

Thank you in advance and looking forward to all of your comments and suggestions.

-Q

 

[mutley1]

Have you had any experience with DTS packages?

You can use an Excel Spreadsheet as a "connection" and in the data pump task you specify your query and the destination will offer you a drop down list of Sheet1, Sheet2 etc. etc. Obviously, your spreadsheet will need to exist on the server so you can point the DTS package connection to it.

I won't go into detail yet as you might have had the experience, but if you need more, let me know.

HTH

M.

 

[qjade]

Thank you for your reply Mutley.

I have attempted at creating two separate views and using DTS to split the results to two separate sheets within a spreadsheet. My next question is how do I pull the column name with the query/view to the sheets as well? Is there a way to utilize the DTS methodology to export to a new spreadsheet (not pre-created) with all the column headings?

Thanks and looking forward to your reply.

 

[mutley1]

OK - couple of questions.

1. Is this a daily report (i.e. you want to start from scratch each day as opposed to appending the data)
2. Do you need to keep a history, or just 1 excel workbook per day?

The way I handle things for my morning reports is:

1. Create a template spreadsheet with the column headings already in it.
2. Have a step in the DTS that uses an xp_cmdshell to copy the template to the name of the report (this way you start from scrtch as opposed to appending)
3. Write the data to the newly copied spreadsheet.

e.g.

exec master..xp_cmdshell 'copy c:\myfolder\mytemplate.xls c:myfolder\mydailyreport.xls'

then use the queries / views to populate mydailyreport.xls.

If you set it up this way, don't forget you will have to have the mydailyreport.xls existing during setup of the package so it knows where it is going to write the data.

HTH,

M.