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:
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.
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.
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 Export options button.
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.
That was simple, wasn't it. Now move on to the next example to learn how to create a slightly more complex Excel report.
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 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.
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.
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.
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.
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.
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.
See Schedule Your Script to learn how to schedule the Automation script.