Automate Exporting to Excel^® Reports

Summary

This tutorial walks you through the process of automating the task of exporting query results to simple Excel reports. You will create three different, and very useful, simple Excel reports.

In this tutorial you will learn:

How to use Automation to export query results to Excel
How to use the Select to File activity in Automation
How to automate an Excel report that appends a new worksheet with each execution
How to automate an Excel report that uses multiple queries to produce multiple worksheets

Introduction

After creating a result set in Toad, you may need to send the results to your end users. The easiest way to create a report containing query results is to export the data to an Excel file by right-clicking the data grid and selecting Quick Export | File | Excel File. This action creates a basic Excel spreadsheet containing the result set from the data grid.

If you want to create more than a basic spreadsheet, and you want to automate the process, Toad Automation helps you build a variety of Excel reports, from simple worksheets to complex and attractive Excel reports.

In this introduction to automating Excel reports, you will learn how to automate the process of exporting data to a variety of simple Excel reports.

Note: To learn how to get started with Automation, see Getting Started with Automation.

Create a Simple Excel Report

Select Tools | Automation.
Click the Select to File activity in the Databases Activity pane of the Toolbox, or drag the activity from the Toolbox to the Automation design window.
When the Select to File activity is selected in the design window, the Activity Input tab displays. Select a database connection in the first text box or use the default (the default is the currently-active open connection).
Then select a SQL file or enter a SQL statement using one of the following methods:
- Manually enter a SQL statement in the editor box (deselect the Link SQL file option).
- Click to browse to and select a SQL file.
- Select Link SQL file, then click to browse to and select a SQL file.
- Click to open the SQL file (you previously selected) in an editor where you can modify the SQL.
- Select Link SQL file, then click the drop-down list to select a file from the currently open project in the Project Manager.
In the Export File Type field, select the Excel File button.
Click in the Name field to select a location and enter a name for your new Excel file, or you can select an existing file.

Note: To specify more-detailed options for the Excel file, click the Advanced Properties button.
(Optional) You can choose to create a unique file by adding a suffix. In the Suffix field, click the drop-down to add a previously-used suffix, or click to build an expression.
Select Overwrite if you want to overwrite the file.

Note: If you did not add a suffix, you can deselect Overwrite to append a new worksheet to the Excel file with each script execution.
To save your Automation script, click in the Automation toolbar. Then select a location and file name for your script.
To test your script, click in the Automation toolbar.
After the script executes, the Log tab displays. Click the hyperlink in the Log to view the Excel file.
To learn how to schedule your Automation script, see Schedule Your Script.

That was simple, wasn't it. Now move on to the next example to learn how to create a slightly more complex Excel report.

Create a Multi-Page Excel Report Using One Query

Suppose you want to create a new report each week from the same query while retaining the previous week’s results. In this case, you want to append each week’s results as a new worksheet to a single Excel file. This is an excellent option if you want to compare the results each week (or each day, month, etc.) to see how the data is changing. (And Toad has several data compare features in the application to let you do that.)

Select Tools | Automation to open a new Automation window.
Click the Select to File activity in the Toolbox to add it to the Automation design window.
Select a database connection.
Select a SQL file or enter a SQL statement using one of the methods described in the first example.
In the Export File Type field, select the Excel File button.
In the Name field, click to select a location and enter a name for your file.
Leave the Suffix field blank (adding a date/time suffix could create a new file with each script execution, and this is not the objective in this example).
Deselect Overwrite (you do not want to overwrite the file).
Now click Advanced Properties. The Advanced Export Properties dialog opens.
In the Worksheet name field, enter a name to be used as the base worksheet name.
Select Append timestamp to named worksheet. This will append the date and time (that the data was exported) to the base worksheet name. Click OK to save your settings and close the dialog.
Click to save and run your script.
Open the Excel file by clicking the link in the Log tab. Notice that the new worksheet’s name includes the base name you specified suffixed with the timestamp.
Run the script a second time and open the file. A new worksheet with a new timestamp was added to the Excel file.

The output of this script is one Excel file containing multiple worksheets. Each worksheet contains a result set from one execution of the script. And each worksheet name records the date and time the result set was exported.

Create a Multi-Page Excel Report Using Multiple Queries

Another variation of the multi-page Excel report is to export the results of multiple queries. Each worksheet in the Excel file will be populated with the result set of a different query. This can be accomplished by using a SQL file that contains multiple SQL statements as the input file.

To start, open a new Automation window.
Click the Select to File activity to add it to the design window.
Select a connection.
Select a SQL file or enter a SQL statement using one of the methods described in the first example. However, this time use a file that contains multiple SQL statements (or enter multiple statements in the editor box).
In the Export File Type field, select the Excel File button.
Click in the Name field to select a location and enter a name for your new Excel file.
Click Advanced Properties and make sure the Worksheet name field is blank.

Note: If the Worksheet name field is not blank, your script will export both result sets into the named sheet, and the second result set will overwrite the first.
Selecting to overwrite the existing file or to add a date/time suffix to the file name is optional in this example.
Click to save and run your script.
Open the new file by clicking the link in the Log tab. Notice that the file contains a separate worksheet for the result set of each query.

The output of this script is one Excel file containing multiple worksheets. Each worksheet contains the result set of a different query.

Note: To learn how to export the result sets of multiple queries into a single worksheet (using Automation), see Automate Complex Excel® Reports.

Learn More

As you can see, with Toad Automation you can easily create basic Excel reports, but with a few more script configurations and the use of templates, you can also create attractive and more complex reports. To learn how, see the following tutorials on Automating Excel Reports.

Schedule A Script

See Schedule Your Script to learn how to schedule the Automation script.