The Data Loader is a powerful tool for loading data into your tables.
you can load data from a variety of sources (csv files, Excel files, tables or queries)
you can load data from a different database, including a database of a different type
you can specify how the data is mapped between the source and the table you are loading
you can "massage" the data - eg. perform simple functions to tidy and transform the data as you load it
you can do a multiple load - eg. load all tables in a schema in a single operation
Can Load from a variety of Sources
The data for your table can come from:
a structured file (such as a csv)
an Excel file. You can even do this if you don't have Excel installed on your PC.
another table or a query. The table/query can be in another database. This makes the Data Loader the ideal tool for migrating data between different database systems.
The following is an example of when the source is an Excel worksheet.
Loading Data from an Excel file
Mapping the data from the Source to the Target
The Data Loader has a powerful mechanism for mapping data to the target table from your source. You can also do some (simple) manipulations of the data.
Click on the Mapping tag to see the mapping window. You use the grids to specify how the columns in your table are to be loaded from the columns in the source.
Load Spec allows you to specify some more complex mapping rules.
FULLNAME is loaded by concatenating columns 2, 3 and 4 from the source
STATUS will be loaded from a constant
HIREDATE has mapping function <7:date(yyyy-mm-dd)>. This will take field 7 and reformat it in yyyy-mm-dd format
The column Sample of Data shows how your data looks with this mapping applied
Specifying how the columns are to be loaded
Preview the Load
Often it is useful to see what data you are loading into the table, before you do the actual load. This is particularly important if you have a complex mapping between the source and target.
The Data Loader has a Preview feature that allows you to see the data that will be loaded. If there are any "obvious" errors in the data (such as a character field being too long, or a numeric field not being numeric) the data value will be highlighted.
Previewing the data to be loaded
The Data Loader is FAST
uses AQT's native ODBC interface, which is optimized for speed
a rate of 2000 to 3000 rows per second is not uncommon
the screenshot below is a real-world load of data from a server in the US to a PC in New Zealand. Over 1.1 million rows loaded in under 4 minutes!
Data Load is Fast
Load Multiple Tables
This feature allows you to load data into an entire schema of tables in a single operation.
you can load your tables from other tables (in the same or another database), a directory of csv files, or worksheets of an Excel file
you can apply a filter to select which tables you want to load
has a Create Table mode in which the tables are created. This makes it fast and easy to take a copy of a schema of tables
Loading all the tables in a schema
What Else?
The Data Loader can handle most data types, including binary fields and LOBs. The Data Loader is one of the few tools on the market that can copy LOB data between databases of different types.
The Data Loader can automatically create the target table. The definition of the target table will come from the source; if this is a text or Excel file, AQT will parse the data to determine the data types of the columns.
The Data Loader can be run in Load-Update mode so that if a row already exists in the target table it will be updated.
The Data Loader also has an Update mode, under which no rows will be loaded into the target table, it will only be updated. This can be used to (for instance) update the values of one of your columns from an external source (such as a file or Excel spreadsheet).
You can save / retrieve your load-specifications. This is useful if you have spent some time building a load mapping and wish to use it later.
The Data Loader can be run in batch (unattended) mode.
