Advanced Query Tool





Data Loader

The Data Loader tool is part of the Extended Edition of AQT.

Specify the Load Source

The data for your load can come from one of a variety of sources: a structured file (such as a csv), an Excel file, another table (which can be in another database), or a query. The following is an example of when the source is an Excel worksheet.

  • Once you select an Excel file, AQT shows you the worksheets in a drop-down list (you need to select one of these).
  • Once a worksheet has been selected, AQT shows you the cells that form the active-area for the worksheet. You can amend these values if you want to load from a smaller range of cells.
Load Select source

Map Target table from Source data

The Data Loader has a powerful mechanism for mapping data to the target table from your source. You can do some (simple) manipulations of the data.
Click on the Mapping tag to see the mapping window. Target fields in the left grid are generated from source fields in the right grid.
  • You can change the order of the source fields by dragging rows (in the right grid) up and down
  • The column Load Spec gives the detailed description of how data is to be mapped. You can define more complex mapping rules. For instance in row 2 below the spec "<2> <3> <4>" will concatenate columns 2, 3 and 4 from the data source, with blanks between the columns.
  • You can load a constant into a column by putting the constant into Load Spec, as in row 3 below.
  • You can perform some functions on data fields. For example <7:date(yyyy-mm-dd)> will take field 7 (which must be a valid representation of a date) and reformat it in yyyy-mm-dd format.
Load Mapping

Preview the Load

Often it is useful to see what data you will be 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 (the window below gives an example of this).

Preview Load

Run the Load

Click Load Data to run the load. Progress messages will be written to the bottom of the window. You can Abort the load at any time.

If there were any errors during the load, an error report will be written to an error file. Go to the Error Handling tab to view the report file. The screenshot below gives an example error file.

In addition, rows that were not loaded due to errors will be written to an error file. The error rows are written to this file in csv format, even when the source data is an Excel worksheet or table. This makes it easy to correct and reload the data.

The Data Loader has comprehensive options for what to do in various error situations - these are set on the Error Handling tab.

Action

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. With this, when a row already exists in the target table, AQT will update this existing row with the values from the source.
  • The Data Loader also has an Update mode. With this, 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 mode.
  Go Home Go to Top

Last reviewed on 18 May 2011   

Copyright 2002-2011 Cardett Associates Ltd. All rights reserved.