Chat now with support
Chat with Support

Toad Data Point 6.2 - User Guide

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 to it.
    • 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.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating