Toad Data Point 5.3 - Installation Guide

Execute an existing SQL script, Query Builder file, or Editor file and export the results to an Excel, HTML, or CSV file.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • Click the drop-down list to select a file from the currently open project in the Project Manager.
   • Click to browse to and select a SQL file.
   • Click to open the SQL file in the Editor where you can modify the SQL file.

   • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.

3. RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
4. Export File Type—Select an export file type.
5. Export File Name—Click to select a file location and enter a file name. To use a variable as a suffix, enter the hash symbol before and after the variable name (#VariableName#).

6. Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

7. Overwrite—Select to overwrite the file. If you did not add a suffix to the file name, you can deselect Overwrite to append a new worksheet to the Excel file with each script execution.

8. Export Options—Click to specify more export options. See Specify Excel Export Options for more information.

   • Worksheet name—Enter a worksheet name. For an existing file, select the worksheet from the list. Overwrite must be deselected to display the list.

     To add a worksheet to an existing file, select Append worksheet from the list and enter a new name.

   • Append rows—Select to append to the selected worksheet.
   • Start export at—Enter the column and row position at which to begin the export.

Tip: After exporting your file, you can use a subsequent activity in your script to email the report, to zip the report in a zip folder, or to copy the report to another location.

Execute an existing SQL script, Query Builder file, or Editor file. You can save the results of the script in a variable or export the results to Local Storage or an Intelligence Central dataset.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • Click the drop-down list to select a file from the currently open project in the Project Manager.
   • Click to browse to and select a SQL file.
   • Click to open the SQL file in the Editor where you can modify the SQL file.
   • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.

3. Save result set in variable—Enter a name for the result variable, which contains a data table with the results of the execution script.

   Tip: You can use the result variable with the Loop Dataset activity. See Use System Automation Activities for more information.

4. Local Storage—To export the results to Local Storage, select a database and a new or existing table (or snapshot).
5. Intelligence Central—To export the results to Intelligence Central as a dataset, click Publishing Wizard and specify your publishing options. See Publish to Toad Intelligence Central for more information.

Review the following for additional information:

• RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
• Overwrite existing dataset—Select to overwrite an existing dataset with the same name.

To learn more about using the Execute Script activity, see the following:

• Automation and Executing Scripts (tutorial)
• Automate Creating and Refreshing Snapshots in Local Storage (tutorial)
• Automate Publishing Data to Intelligence Central (tutorial)

Execute an existing SQL script or Query Builder query and save it and any results sets in a Toad editor file (.tef).

This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.

1. Select a database connection.
2. Select a SQL file or enter a SQL statement.
   • Click the drop-down list to select a file from the currently open project in the Project Manager.
   • Click to browse to and select a SQL file.
   • Click to open the SQL file in the Editor where you can modify the SQL file.
   • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.

3. Result saved in Editor file—You can select an Editor file from an open project or browse to select a file. Click to edit the selected file.

   Tip: You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

4. Append results—Select to include the result set in the Editor file.

Review the following for additional information:

• RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Export data from databases, tables, views, or a query into a CSV, Excel, or other file.

Review the following for additional information:

• Export template—You can select an existing template, edit an existing template, or create a new template.

  Tip: You can use a variable in the file name to select an existing template file by entering the hash symbol before and after the variable name (#VariableName#).

• Use Automation Connection—Select this option to use the connection associated with the Export Wizard activity. Clear the checkbox to use the connection associated with the export template.
• RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

Note: See Export Data with the Export Wizard for more information.

Import data from a CSV, Excel, or other file into a new table or append to an existing table. You can also import data from one database to another.

Review the following for additional information:

• Import template—You can select an existing template, edit an existing template, or create a new template.

  Tip: You can use a variable in the file name to select an existing template file by entering the hash symbol before and after the variable name (#VariableName#).

• Use Automation Connection—Select this option to use the connection associated with the Import Wizard activity. Clear the checkbox to use the connection associated with the import template.
• RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

Note: See Import Data for more information.

Use this activity to open a Toad Data Report (executing the query) and then export the results to a file using the layout specified in the Toad Data Report. Several export file formats are available, including PDF, HTML, and Excel.

You can also select multiple Toad Data Reports and export the results to a single file.

Note: You must create a Toad Data Report file (.tdr) before you can select it using this activity.

Review the following for additional information:

• Select a connection to execute the Toad Data Report query against.
• Add Reports—Click to browse to and select one or more Toad Data Report files (.tdr). Click the arrow and select From Project Manager to add the reports in the currently-open project.
• Edit Report—Click to edit the selected report.
• RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
• Export file—Enter a name and select a file type and location for your export file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Note: In most cases, the script uses the connection you specify in this activity. However, when a Toad Data Report contains a cross-connection query, the cross-database connection in the report takes precedence.

For more information about Toad Data Reports:

• See About Designing Toad Data Reports to learn how to create a Toad Data Report.
• See Automate Toad Data Reports (tutorial).
• Watch Automation and Toad Data Reports (video).

Open an existing Toad Pivot Grid file, execute the query, and export the data to Excel or other file format, Local Storage, or Intelligence Central.

Note: You must create a Toad Pivot Grid file (.tpg) before you can select it using this activity.

See Pivot and Chart Data for more information about creating a Pivot Grid.

Review the following for additional information:

1. Select a connection to execute the Toad Pivot Grid file against.
2. Select an existing Toad Pivot Grid file. You can browse for a file or select from the currently-open project. Click to edit the selected pivot file.
3. Export to file—Select a file type and enter a file name and location for your export file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).
   • To export to an Excel pivot table, select Excel Pivot. See Pivot and Chart Data for a description of export file types.
   • Click Export Options to specify Excel file options. See Specify Excel Export Options for more information.

4. Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

5. RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
6. Export to—Select one of the following:
   • Local Storage—To export pivoted data to Local Storage, select a database and a new or existing table.
   • Intelligence Central—To export pivoted data to Intelligence Central as a dataset, click Publishing Wizard and specify your publishing options. See Publish to Toad Intelligence Central for more information.

Automate data profiling tasks. This activity profiles data from a selected query and exports a report containing the profiling results.

1. Select a database connection.

2. Select a SQL file or enter a SQL statement.

   • Click the drop-down list to select a file from the currently open project in the Project Manager.
   • Click to browse to and select a SQL file.
   • Click to edit the selected file.

   • Enter a SQL statement in the editor.

3. To specify options for the profiling task, click Profiling Options. See Profiling Options for more information.
4. Create Report—(Required) Select a location, select a file format, and enter a file name for the exported report (PDF, HTML, etc).

5. Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

6. Export Data Profiling File—(Optional) Select a location and enter a file name for the Toad Profiling Report file (.tpr). See Data Profiling for more information.

   Note: You cannot attach this file in subsequent activities in the script.

Automate data transformation and cleansing tasks. This activity opens an existing Toad Data Cleansing file, performs the transform and cleansing tasks on the current result set, and exports the modified data to a file, Local Storage, or Intelligence Central.

1. Connection—Select a database connection.
2. Cleaning file—Select a Toad Data Cleansing file or edit an existing file using one of the following methods:
   • Click the drop-down list to select a file from the currently-open project in the Project Manager.
   • Click to browse to and select a file.
   • Click to edit the selected file.
3. Error options—Click to specify error options. See Transform and Cleanse Options.
4. Select one of the following actions:
   • Export to a file—Select a file, or select a location and enter a new file name. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).  See Specify Excel Export Options for more information.
   • Local Storage—To export to Local Storage, select or create a database. Then select or create a snapshot or table to receive the modified data.
   • Intelligence Central—To export the results to Intelligence Central, click Publishing Wizard and specify your publishing options. See Publish to Toad Intelligence Central for more information.

Review the following for additional information:

• RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

See Transform and Cleanse Data for more information.

Open an existing Toad Data Visualization file (.tdv), refresh the chart, and export contents as a report. Select from a number of output file formats, including PDF and Excel.

• Data Visualization document—Select an existing Toad Data Visualization file.
• Output file—Select a file location, file name, and file type for your output file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).
• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

See Visualize Data for more information.

Open a Toad Data Compare project (.dcp) and export the contents as a summary report and detailed object reports. Select from a number of output file formats for the summary report, including PDF and Excel.

• Data Compare Project—Select an existing Toad Data Compare project file.
• Summary Report—Select a file location, file name, and file type for the comparison summary report file.
• Object reports folder—Select a destination folder for the detailed object comparison reports generated by the activity.
• Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.
• Generate sync script—Select a location and enter a file name to generate a synchronization script. Click to configure options for the synchronization script.
• Execute script—Select to execute the generated script.
• Compare result variable—Enter a variable name or use the default. This variable is assigned a value of True or False based on the results of the data comparison.

Use this activity to refresh a snapshot in Local Storage or in Toad Intelligence Central (TIC). This action will update the snapshot to reflect the current data in the source table. See Save Data to Local Storage or Snapshots in TIC for more information.

Select one of the following:

• Local Storage
  • Database—Select the database containing the snapshot to refresh.
  • Snapshot—Select the snapshot to refresh.
• Intelligence Central
  • Select a destination—Select the TIC connection containing the snapshot to be refreshed.
  • Datasource—Select the data source in TIC containing the snapshot to refresh.
  • Snapshot—Select the snapshot to refresh.

To learn how to use the Refresh Snapshot activity, see the following:

• Automate Creating and Refreshing Snapshots in Local Storage (tutorial)
• Automate Refreshing Snapshots in Intelligence Central (tutorial)

In previous versions of Toad, the Database Connection activity was required in order to specify database connection details.

In the current version of Toad, you can specify the database connection details within each applicable activity, eliminating the need to use the Database activity. You may continue to use the Database activity if you prefer.

Note: The database connection specified for the Database Connection activity takes precedence over the connection specified for the individual activity.

Loops through multiple connections and executes the activities you specify. Add as many activities as you want to the loop. The Loop Connections activity can have more than one branch.

• Select connections to loop through—Click Add Connections to select one or more connections to loop through.

• Connection description variable—Enter a new name for the variable or use the default name. The connection description variable is a SQL-type variable comprised of one row containing the connection information for the current connection.

  Use the following column names to gather connection information from the connection description variable. The exact content of each column depends on the provider type.

  Column Name	Description
  DescriptionLong	Description of the connection details.
  ProviderType	Provider type such as SQLServer, Oracle, or SAP ASE.
  HostName	Hostname or instance for the connection.
  UserName	User name.
  TrueUserName	If the connection supports and uses Windows authentication (e.g., SQL Server connection) this column contains the current Windows Logon name as: domain\username (e.g., "PROD\bgates"). Otherwise, if database authentication is used, it contains the same as the UserName column.
  Database	Default Database context for the connection.

• Next, add activities to the loop to execute for each connection in the connection list.

Note: The connections you specify in the Loop Connections activity take precedence over any connection specified in an individual activity.

Open an existing Dimensional View file, execute the query, and export the data to Excel, Local Storage, or Intelligence Central.

1. Select an existing Dimensional View file.

2. Export to file—To export data to Excel or .csv, select a file type, a file location, and a file name. Click Export Options to specify export options. In the Export dialog, each tab represents a view. See Specify Excel Export Options for more information.

   • Worksheet name—Enter a worksheet name. For an existing file, select the worksheet from the list. Overwrite must be deselected to display the list.

     To add a worksheet to an existing file, select Append worksheet from the list and enter a new name.

   • Append rows—Select to append to the selected worksheet.
   • Start export at—Enter the column and row position at which to begin the export.
3. Export to:
   • Local Storage—To export data to Local Storage, select a database.
   • Intelligence Central—To publish the data to Intelligence Central as a dataset, click Publishing Wizard and specify your publishing options. See Publish to Toad Intelligence Central for more information.

See Dimensional Viewer for more information.

Notes: 

• To disable an activity, right-click the activity in the design window and select Disabled. This is helpful if you want to disable an activity while testing other activities.
• The Stop on Error option for each activity allows you to select whether to stop or continue execution if Toad encounters an error during that activity.
• To prevent an error in one activity from causing a failure at the script level, clear the Generate Error check box (Activity Info tab) for that activity. See Automation Script Troubleshooting and Error-Handling.
• Select the Log tab to review a script's execution log.
• In the Suffix field, the drop-down list includes two predefined suffixes (Date and DateTime). The list also includes any suffixes you have created and saved since installing Toad. To delete a suffix from the list, select the suffix, click , delete the suffix in the Expression field, and click OK.
• Automation does not support positional parameters denoted by a question mark.

• For each applicable activity, you can specify the database connection in the activity's properties. The default connection is the currently-active open connection.
• The connection associated with an activity takes precedence over the connection information in a report or SQL script, unless the report or SQL script information is a cross-connection query.