In our last release we went through our export to file matrix. We made some changes and documented the matrix below. If you feel we are not following the matrix below or that we missed a scenario can you post your automation script and output files so we can review? If you do not want this shown on the forum then you can make a support case and we will pick up this topic.
More About Excel Export Options
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.
Using the Overwrite and Date/Time Suffix Options
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 the Date/Time Suffix option is selected, the previously-created suffixed files are retained.
- If you want to export to two or more named worksheets, use the Export Wizard with scenario 3. Do not use multiple queries in one SQL script (scenario 2).
-
Append worksheet is the default setting.
Append Worksheet with no Overwrite
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:
- Suffix—When a file name suffix is added, Toad creates a copy of the original file and then exports to it.
- No Suffix—If no suffix is added, with each execution Toad adds a new worksheet to the working file and then exports toit.
- Default—If the default Worksheet name is used (Sheet#), Toad adds Sheet 2, Sheet 3, etc.
- Timestamp—If you select Append timestamp to named worksheet, Toad adds a timestamped worksheet.
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.
