Data Loader

Available in AQT Extended

This feature and a few others are available only with an Extended Edition license

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.