This tutorial teaches you how to automate the task of exporting data using the Export Wizard activity. You will build a script that handles a special case of exporting to a .csv file and one that exports to a Microsoft Access® database.
In this tutorial you will learn:
You will need:
Exporting data is a common task and a great candidate for Automation. Automation's Select to File activity can handle basic exporting tasks. However, for more complex or special situations, you must use the Export Wizard activity.
A comma separated values file (.csv) is the most common format to use when exporting a large number of rows. A .csv file can handle millions of rows, while an Excel file does not handle a large number of rows as efficiently.
For most tasks that require exporting to .csv, you can use the Select to File activity. However, if your data contains commas or quotation marks embedded in the cell values, you must specify special quote characteristics. To do that, you must use the Export Wizard activity.
On the Activity Input tab, deselect the Use Automation Connection option. This instructs Toad to use the connection associated with the Export Wizard template instead of the one associated with the Export Wizard activity.
On the Select Objects page, select Query as the Export Category, and enter your query in the editor box.
Alternatively, you can browse to and select a query file. The query text displays in the editor box where you can modify it, if necessary.
Note: If you want to link a query file to the template, select Query File. Then browse to and select the file. This is useful if you need to change the query frequently, but don't want to change the template.
Then, in the Quote character field, enter a character that you know is not included in the quoted strings (cell values). For this example, enter the pipe (|) character. Click Next.
On the Save Settings as Template page, select the Save template option. And then select a location and file name for your Export Wizard template.
If you want to use the Export Wizard template that you created, but you want to change the database from which you want to export data, you can change the database connection without changing the template.
The Select to File activity only allows exporting to a file. The Export Wizard activity, however, provides a number of other output options, including exporting to an Access database.
On the Select Objects page, select Table as the Export Category, and select a table from the list. Click Next.
The Export Wizard provides many more export options than the Select to File activity. Use the Export Wizard activity when you need to specify more than just the basic export-to-file requirements.
See Schedule Your Script to learn how to schedule the Automation script.
Note: To learn more about the Export Wizard, see Export Data with the Export Wizard.