Automation scripts are a powerful and useful tool in Toad. For many users, the end result of their script is an Excel report. Toad allows you to specify a number of options for the exported Excel report. The outcome is determined by the combination of settings specified in the Export options dialog.
This topic focuses on how to use some of the settings in the Export options dialog and the Export Wizard. To review the Export options dialog, see Specify Excel Export Options. To review the Export Wizard, see Export Data with the Export Wizard.
The following table provides some common use cases involving the Export Wizard or the Select to File activity. In particular, this table describes the expected outcome when different Overwrite and Date/Time Suffix settings are used. In these example scenarios, the output is a single file.
Scenario 1. Export Wizard—One object or one query
Export Options | Output (single file) | ||||
---|---|---|---|---|---|
Overwrite | Suffix | Toad | File Name | Comments | |
1a | Y | Y | Creates a new blank file (with suffix) and exports to it | original name + suffix | If identical suffixed file name exists, it is deleted. |
1b | Y | N | Creates a new blank file and exports to it | original name | Deletes the original file. |
1c | N | Y | Copies original file, adds a suffix, and exports to it | original name + suffix |
See the Append worksheet examples below. |
1d | N | N | Exports to original file | original name |
See the Append worksheet examples below. |
Scenario 2. Export Wizard—Two queries (in one SQL script) or Select to File—Two queries
Export Options | Output (single file) | ||||
---|---|---|---|---|---|
Overwrite | Suffix | Toad | File Name | Comments | |
2a | Y | Y | Creates a new blank file (with suffix), exports 1st result set, then exports 2nd result set to same file | original name + suffix | If identical suffixed file name exists, it is deleted. |
2b | Y | N | Creates a new blank file and exports to it | original name | Deletes the original file. |
2c | N | Y | Copies original file, adds a suffix, and exports to it | original name + suffix |
To ensure that each result set is exported to a separate worksheet, select Append worksheet with no custom name. Otherwise, both result sets are exported to the same selected worksheet. See the Append worksheet examples below. |
2d | N | N | Exports to original file | original name |
To ensure that each result set is exported to a separate worksheet, select Append worksheet with no custom name. Otherwise, both result sets are exported to the same selected worksheet. See the Append worksheet examples below. |
Scenario 3. Export Wizard—Two objects or two queries (as individual Query objects)
Export Options | Output (single file) | ||||
---|---|---|---|---|---|
Overwrite | Suffix | Toad | File Name | Comments | |
3a | Y | Y | Creates a new blank file (with suffix) and exports to it. | original name + suffix |
If identical suffixed file name exists, it is deleted. Each object has its own options. |
3b | Y | N | Creates a new blank file and exports to it. | original name |
Deletes the original file. Each object has its own options. |
3c | N | Y | Copies original file, adds a suffix, and exports to it, one object at a time. | original name + suffix |
See the Append worksheet examples below. Each object has its own options. |
3d | N | N | Exports to the original file, one object at a time. | original name |
See the Append worksheet examples below. Each object has its own options. |
Notes:
When you are appending a worksheet and not overwriting the file, each Suffix setting produces a different output. Review the output for each Suffix setting:
Review the following examples of output files for each Automation scenario.
Scenario | Append worksheet | Over-write | Suffix | Original File Contents | After 1st execution | After 2nd execution | After 3rd execution |
---|---|---|---|---|---|---|---|
1 Query (1c) |
Y | N | Y | Sheet 1 | Sheet 1, Sheet 2 | Sheet 1, Sheet 2 | Sheet 1, Sheet 2 |
2 Queries (2c, 3c) |
Sheet 1, Sheet 2 | Sheet 1, Sheet 2, Sheet 3, Sheet 4 | Sheet 1, Sheet 2, Sheet 3, Sheet 4 | Sheet 1, Sheet 2, Sheet 3, Sheet 4 | |||
1 Query (1d) |
N | Sheet 1 | Sheet 1, Sheet 2 | Sheet 1, Sheet 2, Sheet 3 | Sheet 1, Sheet 2, Sheet 3, Sheet 4 | ||
2 Queries (2d*, 3d*) |
Sheet 1, Sheet 2 | Sheet 1, Sheet 2, Sheet 3, Sheet 4 | Sheet 1, Sheet 2 Sheet 3, Sheet 4, Sheet 5, Sheet 6 | Sheet 1, Sheet 2 Sheet 3, Sheet 4, Sheet 5, Sheet 6, Sheet 7, Sheet 8 |
* For best results, consider using the Append timestamp to named worksheet option to append a worksheet with each script execution.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center