Automation: how to create a single excel file with result of 3 queries

Toad Data Point
1

Hi,

I’m just struggling to create a single excel file with multiple worksheets (excel instances) via automation.

I have 3 different sql queries.

The result of each query should be exported into a separate worksheet.

But as final result I want to have only ‘one’ excel file with 3 worksheets in it.

Regards
Wolfgang

2

If you combine the queries into a single sql statement, you should be able to run the automation activity as a “Select To File”. The attached automation script shows an example of this. My SQL statement contains 3 separate queries that are separated with semi-colons. When I export the results to Excel, the automation automatically splits the results of each query to a separate worksheet in Excel. I’m also attaching the silly example SQL that I used for the automation. It’s labeled 3sql.sql.

Hope this helps.
SepWrkshts.tas (7.4 KB)

3

If you combine the queries into a single sql statement, you should be able to run the automation activity as a “Select To File”. The attached automation script shows an example of this. My SQL statement contains 3 separate queries that are separated with semi-colons. When I export the results to Excel, the automation automatically splits the results of each query to a separate worksheet in Excel. I’m also attaching the silly example SQL that I used for the automation. It’s labeled 3sql.sql.

Hope this helps.
3sql.sql (145 Bytes)

4

You don’t need to combine the sql files into one, just create an automation file with a ‘select to file’ for each query. Keep the excel file name the same and just change the worksheet name. There are more steps involved but if you dont want to combine the sql files you can do it this way too.
SelectToFile.jpeg

5

This is great to know. I was also wondering how I could combine about 7 different queries into one excel sheet export. Currently I am able to do this with Forest and Trees (old program) using views to run different queries and then combine them all with one last view that references the other 6 queries to combine the data into one formatted export. Sounds like a lot of work I know.

Regards,
Jim

6

The above two methods are the only ones we have for building a multiple query - single Excel export report. That and the use of macros.

To summarize, here are the methods.

  1. Put all queries into a script and use the Add Query Export wizard.

select * from a;
select * from b;
select * from c;

When exported the result from each query will be put into a new worksheet starting at the upper leftmost cell and row.

  1. Export a single query to a specific worksheet name, starting cell, or named region.

Here you can use the add query in Export. In the configuration page choose an existing named range, or named worked sheet, or select a specific cell/row to place the data.

  1. For additional formatting you will need to add a Macro. The macro can be called before or after the export.

Between these three methods it covers most needs.

Let me know if you need more help on this. If so, you will probably need to post some examples so I can get more detail on what you want to accomplish.

Debbie

7

I want some more detail on multiple queries with my particular situation so I will start a new thread. Thanks.

Jim