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 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 Database Activities 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 (deselect the Link SQL file option).
- Click
to browse to and select a SQL file.
- Select Link SQL file, then click
- 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.
- Select Excel in the Type field in the Export to File section.
-
Click
Note: To specify more-detailed options for the Excel file, click the Export options button.
- (Optional) You can choose to create a unique file by adding a suffix. In the suffix field, click the drop-down to select a datetime stamp, 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
- To test your script, click
- 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.
- Select Excel in the Type field in the Export to File section.
- In the Name field, click
- 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 of this example).
- Deselect Overwrite (you do not want to overwrite the file).
- Now click Export options. The Export options 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
- 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).
- Select Excel in the Type field in the Export to File section.
- Click
-
Click Export options 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
- 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.
- Automate Complex Excel® Reports
- Automate Excel® Pivot Table Reports
- Automate Excel® Macro-Enabled Reports
Schedule A Script
See Schedule Your Script to learn how to schedule the Automation script.