Summary
This tutorial teaches you how to automate the task of importing data into a database. You will build a script that uses the Import Wizard activity. First, the tutorial teaches you how to create three different Import Wizard templates, and then how to automate them.
In this tutorial you will learn:
You will need:
Multiple files containing data that you want to import into your sample database
Note: The files must be in the same folder and have the same file extension and the same configuration (same number of columns, same data types, etc.).
Watch Automation and Importing Data to see a video presentation of this tutorial.
At times you may need to import data into a database, such as a production database
For this tutorial, you will want to use a sample database or a sandbox area.
You will begin by creating three different Import Wizard templates. Each one imports data from a different source and can be used in a separate Automation script.
Connect to your sample database (or the database into which you want to import the data).
Note: The Import Wizard automatically uses the currently-active open database connection. So, if you have multiple database connections open, you must select the database into which you want to import data as the active connection before opening the Import Wizard.
Select the sheet to import and specify the import options, then click Next.
Click Next when finished.
Click Next when finished.
On the Import File page, click Next.
Note: If you have additional files from which you want to import data, click Add File on the Import File page and then specify import, column, and target options. Repeat the process for each file.
On the Save Settings as Template page, select the Save template option to save your Import Wizard settings as a template.
Click to select a location and name for your new template. You will use this template with the Import Wizard activity later in this tutorial.
In this template you will use multiple files (located in one folder) as your data source. Using this option of the Import Wizard allows you to select all files of the same file type and the same configuration (same number of columns, same data types, etc.) in one folder. You can also narrow your selection by file name or date modified. This is very useful if you need to import data from a large number of files.
Connect to your sample database (or the database into which you want to import the data).
Note: The Import Wizard automatically uses the currently-active open database connection. So, if you have multiple database connections open, you must select the database into which you want to import data as the active connection before opening the Import Wizard.
Note: The filters you select are applied at execution time, so Toad uses the most-current files in the directory, including new files that match the filter criteria.
Click Next when finished.
On the File Preview page, specify the import options (see Template 1 for more details). Click Next when finished.
Note: The import options you specify will be used on each and every file. That is why each file must have the same configuration.
On the Save Settings as Template page, select the Save template option to save your Import Wizard settings as a template.
Click to select a location and name for your new template. You will use this template with the Import Wizard activity later in this tutorial.
In this template you will import data from one database to another database. The databases can be similar or different platforms. For example, you could import a table from an Oracle database into a SQL Server database. The Import Wizard handles creating the table and columns in the SQL Server database.
Connect to your sample database (or the database into which you want to import the data).
Note: The Import Wizard automatically uses the currently-active open database connection. So, if you have multiple database connections open, you must select the database into which you want to import data as the active connection before opening the Import Wizard.
Enter a query in the editor box or click Open File to select a file.
Notes:
On the Save Settings as Template page, select the Save template option to save your Import Wizard settings as a template.
Click to select a location and name for your new template. You will use this template with the Import Wizard activity later in this tutorial.
Now build a script using one of the Import Wizard templates you created.
Click
As an alternative method, you can use the Import Wizard activity to create your template. To do this, add the Import Wizard activity to your script. In the activity, click to open the Import Wizard. Step through the wizard using the instructions listed above for the type of template you want to create.
Background: When using the Import Wizard, if you choose to import data into a new table, Toad reads the first 1000 rows and uses this data to assign the data types that you see displayed on the Select Target page. If your data includes a VARCHAR field, Toad assigns a VARCHAR length based on the longest VARCHAR entry in the first 1000 rows of that column.
Issue: If your full data includes a VARCHAR entry that exceeds the length assigned by Toad, you will encounter an error when importing the data.
If the Import Wizard template is part of an Automation script, the script will fail to import that data value.
Workaround: If you know the length of your VARCHAR field, when creating the Import Wizard template, on the Select Target page, manually edit the data type to reflect this length. Or, if the template or script errors, open the Import Wizard template and modify the data type to reflect this length.
See Schedule Your Script to learn how to schedule the Automation script.
Note: See Import Data for more information about using the Import Wizard.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center