Data Loader

The Data Loader is AQT's main tool for loading data into tables. The data can comes from another table, in either the same database or another database, from a csv file, or from an Excel worksheet.

Select load source

Use the left pane of the Source Tab to select the type of data to be loaded, then specify the source on the right.

Four types of data source can be specified:

File

• specify the name of the file by using the Browse button or the drop-down list of recent files.

• You can browse/edit this file by clicking on the Edit button. This opens the file using Notepad.

• Alternatively, you can view the contents of the file by clicking on View. The file is split into "fields" according to the delimiters you have specified; this makes it very easy to view the contents of a structured file. It also enables you to see a particular record-number (which is useful if you are getting an error loading a particular record).

• Column Delimiter specifies the character that delimits separate fields in your file. If you have a tab-delimited file, enter the word tab in this field.

  You can specify a hex column delimiter with 0xaa, where aa is the ascii code. Example: 0x5E.

• String Delimiter specifies the character that encloses string values in your file.

• First Line has Column Names specifies that the first line in your file has column names, and not data. If you want the load to bypass more than the first line, see Start from Row on the Options tab.

• File Encoding. This allows you to specify the Encoding of the data in your file. This is useful if your file uses a non-standard encoding. Clicking on View or Preview will show you the data so you can see whether it has been read correctly.

• you can load from a space delimited file by coding a delimiter of space. A space delimited file has values separated by one or many spaces. If the values contain spaces, they need to be enclosed in the string-delimiter character. If a value is missing it needs to be coded as an empty string using the string-delimiter (eg. "").

Excel worksheet

• specify the name of the file by using the Browse button or the drop-down list of recent files.

• once you have selected a file, Excel will be activated and AQT will fetch the list of worksheets in this Excel file. These will be displayed in the worksheet dropdown list. Select the worksheet containing your source data.

• once you have selected a worksheet, the active cells in the worksheet will be displayed in the worksheet Active Area boxes. You can change these values to change the cells that you want loaded into your table.

• clicking on View will show you the worksheet data. This is described in the previous section. Note that this will only show you the data as per the Worksheet Active Area.

• First Line has Column Names specifies that the first line in your file has column names, and not data.

• sometimes Excel gets into a strange state: it will be running but not visible. Use the Activate Excel button to make Excel visible.

• use Open Worksheet as Readonly to open the worksheet in read-only mode. This useful to prevent the load failing because the Worksheet has been opened by other users.

• Format Date values as yyyy-mm-dd. By default, when AQT detects that a value is a Date, it will format it in yyyy-mm-dd format. If this is not done, the values can come through in a format not suitable for loading into a table.

  This option can cause a problem in some circumstances. AQT can sometimes think that a non-date value is a date, and will therefore reformat it when this is not wanted. To prevent this from happening, de-select this option.

Use Native Excel Interface

By default, this option is not selected. In this case, AQT will read the Excel file by:

• starting MS Excel

• passing commands to it

This method is slow and can sometimes be unreliable if Excel gets into an odd state.

When this option is selected, AQT will use a Native interface to Excel. This method is fast and reliable.

• AQT reads the file directly

• you do not need Excel installed on your PC. In other words, this option gives you the ability to load data from Excel files even if you do not have Excel installed.

Use Raw Values

With Excel, there are two choices of how the data values are read:

• Raw. The underlying value of the cell will be used.

• Formatted. The formatted value of a cell will be used. This is the value as you see it when you are viewing the worksheet in Excel.

For instance, the raw value might be 23.4521 whereas the formatted value is $23.45

For compatibility with previous versions, when running a batch script and this is not specified:

• when Native Excel interface is specified, raw values will be used

• when Native Excel interface is not specified, formatted values will be used

Table

• select the table by using the Browse button or the drop-down list.

• select the database from the Database drop-down list.

• Use same schema and table name as target table and Use same table name as target table are useful when you are loading data into many tables. These will automatically set the table name when you select another target table. When Use same schema and table name as target table has been specified, the source schema and table name will be set to the same as the target schema and table name. You might use this, for instance, if you are copying data between tables with the same names in different databases. When Use same table name as target table is specified, only the source table name (and not schema name) is set to the same as the source table. You might use this, for instance, if you are copying data between tables in two different schema in the same database.

Query

Enter the query into the Query text box (copy and paste the SQL from your query), and select the database from the Run query against database drop-down list.

Map to target from source

This tab is used to specify how the data in the Target table is loaded from the data in the load source.

In the LEFT pane

• this shows the columns in the Target table. This list is populated once the Target table is specified.

In the RIGHT pane

• the Source Columns will be populated once the load source has been specified.

• by manipulating the entries in the right grid, you specify how each Target Column is to be loaded. As you change the entries, a sample of the data will be displayed in the right-most column of the grid.

• You can move columns up and down in the right grid. Click and drag the grey grid-numbers to move the grid-rows. This is very useful if the columns in your target table and source are in different orders.

• The most important column in the right grid is Load Spec. This says how the Target Column is to be loaded. For instance <4> says the target column is to be loaded from the fourth column of the load source. You can amend Load Spec to define more complex mapping and formatting rules. This is discussed in more detail later.

• Use Insert Row to add another row to the right grid. This is useful if your target table has more columns than the source table.

• Use Delete Row to remove a row from the right grid. This is useful if your target table has fewer columns than the source table.

• The Default Extra button is useful if the target table has many extra rows than the source. This button will add rows to the right grid so it has as many rows as the left grid. Each extra column will be loaded with a default value for its data type.

• The Reset button will reset all the Load Specs to their default values (which is <1>, <2> etc).

• When you change the load source, the entries in the right grid will be reloaded to reflect the new load source. However the Load Spec will not be changed. This allows you to change the load source to another file/table, without loosing any complex mapping you have set up. Click on the Reset button the reset the load spec to the default values.

Automatic Mapping

The Auto button can be used to Automatically map source and target columns. This mapping is based on column name. When this button is clicked, AQT will go through all the columns in the target table and see if there is a column in the source with the same name. If so, the mapping for the target column is changed to map to that source column. If the target column is not found in the source, the mapping for the target column is left unchanged.

This function is very useful if you are loading between tables which have the same columns, but they are in different orders.

Define load specifications

Load Spec gives the Load Specification for the table column. By default this will be a value in angle brackets such as <4>, which means column 4 from the source. You can specify other things here to manipulate the data, for example:

The Load Spec can have any mixture of text and source column specifications.

The way AQT processes these is quite simple: AQT will scan the load spec and replace any source column specifications with the data from the source. The source column specification is in the format <colno> or <colno:func>. Func applies a function to the column value.

Valid functions that can be specified are:

Let us know at support if there are any other functions that you might find useful (they are easy to implement).

Date

Date takes any string that Windows can interpret as a date, time or timestamp, and formats it as defined by datespec. This is a useful function when copying date, time and timestamp values between databases of different types.

Datespec can be any combination of yyyy (year), MM (month), dd (day), HH (hour), mm (minutes), ss (seconds) and xxx (fractions of a second). For compatibility with previous versions of AQT, nn can also be used for minutes,

In addition, there are a number of pre-defined formats for the common date / time / timestamp formats.

If no datespec is coded, AQT will use a datespec of dt1. This is the timestamp format used by most databases. It is expected that dt2, dt3, and dt4 would be rarely used.

If you are coding your own datespec, you will need to use [colon] rather than a colon, as the colon is used to delimit multiple loadspecs (this is discussed later). Example:

yyyy-MM-dd HH[colon]mm[colon]ss

Scale

Scale is used when loading timestamp values and gives the number of places after the decimal point. Example:

date(dt1,6) would result in 2010-12-31 13:14:15.123456

If the scale is not given, the number of decimal places used is the same as for the input data.

You can also specify scale by using xxxx as part of the datespec. The following two Date functions give the same result:

date(dt1,3)

date(yyyy-MM-dd HH[colon]mm[colon]ss.xxx)

Mask

This function provides a simple and powerful method for manipulating a string value. It is particularly useful for changing the order of values in a string.

You pass to the Mask function a mask string - this says how the data in the result string is to be formatted. The mask string consists of a series of characters:

• a lower-case letter. The character in the result-string will be taken from the nth character of the input string, where n is the alphabetical position of the letter. For instance a specification of cab will convert Joe to eJo (third character, first character, second character). You cannot refer to any character beyond the 26th.

• % - copy all the remaining characters from the input string to the result string

• any other character - copy this character to the result string

The following table gives an example of how this is used.

Trans

The trans function is used to translate string values. In the example of trans(abc,def), AQT will replace all occurrences of a in the string with d, all occurrences of b with e, and all occurences of c with f.

• If the second string is shorter than the first string, the values are removed from the string.

• If your translate-strings have any special characters (such as a comma, blank, brackets etc), enclose your translate-strings in double quotes (eg. trans("abc","def")).

• For other special characters, see the section below on Special Characters.

Special Characters

You can specify special characters using one of the following codes. It is useful to use these as some of these characters (quotes, brackets, colon) are used in the syntax of the load specs, so including them in a function can cause AQT to interpret the load spec incorrectly.

Example:

Include

If there is a column in your table from which you do not want data to be loaded, de-select the Include check-box for that column. The column will not be included in the load. Instead, the data will be taken from the default or auto-generated value for the column.

Using more than one function

You can "string" a number of functions together. For instance:

<2:left(5):lcase>

This will do the left(5) on column 2, then do the lcase on the result.

You can string together up to 5 functions.

nib and nis

nis was introduced to deal with the following problem. Suppose you want to transform a date using mask, but to load null when the value is blank. You might try to code:

<2:mask(abcd-ef-gh):nib>

This doesn't do what you want; when <2> is blank <2:mask(abcd-ef-gh)> is ' - - ' which nib doesn't recognise as blank.

In this case use nis, eg: <2:mask(abcd-ef-gh):nis>. nis the same as nis except that it looks at the original value of the column <2> rather than the value as transformed by the previous function.

Load Method

The Data Loader has three different methods of loading data.

You would use whichever one runs fastest for your database type, network speed and other factors. If doing large loads you may wish to run some test loads to determine the method which runs fastest in your environment.

Default Method

The default load method is to use a parameterized Insert statement. Example:

insert into table (col1, col2, col3, col4) values (?, ?, ?, ?)

AQT will prepare this statement then, for each row being loaded, will supply the values for the parameters and execute the statement.

This method runs fast as the database will only need to interpret the statement once (during the prepare). The subsequent executes run fast.

Loading Using Inserts Statements

With this method, AQT an runs individual Insert statement for each row being loaded.

In some circumstances this is a preferrable method:

• some databases do not process the prepare / execute method efficiently

• some unusual data types can have trouble being passed through a parameter. Using Load Using Inserts often provides a more reliable method of loading.

You specify this load method by (on the Options tab) selecting Load using Insert statements.

Bulk Inserts

This is a variation of Load Using Inserts.

When this is specified, AQT loads multiple rows per Insert statement. Example:

insert into table (col1, col2, col3) values ('A',1,'a'), ('B',2,'b'), ('C',3,'c'), ('D',4,'d')

This statement loads 4 rows in a single operation.

This provides a faster method of loading than Load Using Inserts, however:

• not all databases support this type of Insert statement

• if there is any invalid data value or duplicate row then the Insert statement gets an error and none of the rows are loaded. This makes this option more suitable for loads which are likely to run without error.

You specify this load method by (on the Options tab) selecting Load using Inserts plus Use Bulk Inserts.

You can specify the number of rows AQT will load in a single Insert with Max insert rows.

Load Mode

The Data Loader has three different modes of operating. These are specified on the Options Tab > Load Method > Mode

• Load. New rows are added to the table. If a row already exists with the given key, the row is not loaded or changed in the table.

• Load-Update. New rows are added to the table. If a row already exists with the given key, the existing row in the table is updated with the new values.

• Update. The Data Loader does not add any new rows to the table. Instead, the existing rows in the table are updated with the values from the load source.

Load-Update is useful for applying new or changed rows to a table. Note however that it should not be used to resync the contents of two tables, as load-update will not remove deleted-rows. To resync the contents of two tables, use the Resync option of the Data Compare tool.

Update mode is useful for populating a column of a table from another table, a query or a file. This is a problem that DBAs and developers are often faced with, and can be difficult to do by other methods.

Key (for Updating)

If you have specified a Mode of either Load-Update or Update, AQT needs to know the unique key of the target table. AQT will get this automatically if your table has a primary key, unique key, or user-defined key. Otherwise you will need to specify this.

If using Load-Update, your target table must have a primary or unique key defined. AQT first tries to insert a row; when this insert fails it then tries to update. If the table does not have primary or unique key, the insert will not fail, instead you will get duplicate rows loaded.

Update using Update statements

This is a similar option to Load using Insert statements, and applies to how AQT will run the updates.

During testing of the Data Loader, we have found that some databases have trouble when there are two parameterised queries open at the same time. This is particularly true when LOBs are being loaded / updated. If you are having trouble running the Data Loader in Load-Update mode, you could try selecting the Update using Update statements option.

Foreign Key violation

When loading data into a table, AQT checks the response from the Insert for the "row already exists" error code. In Load-Update mode, AQT will then try to update the row. However some databases give the same error code for both Foreign Key violation as they do for the "row already exists" error code (Primary Key violation). In other words, the reason the data cannot be inserted is because the data does not satisfy a Foreign Key constraint, not because the row already exists in the table. When AQT tries to update the existing row in the table, it will not be found. This will be reported as an error in the Data Loader.

Use IDENTITY_INSERT

This option is used when you are using Sybase or SQL Server, and your table has an identity column. Normally, data cannot be loaded into an identity column. If you wish to load data into the identity column, check this option. AQT will set the IDENTITY_INSERT option before loading the table, allowing identity columns to be loaded.

If you do not wish to load values into the identity column (but have it generate a new sequence), de-select the column on the Mapping tab.

Load using Insert Statements

When Load using Insert statements is specified, AQT uses a separate insert statement per row being loaded.

Generate Script

You can write the SQL statements to a script file by checking the Generate script option. The script file used is loadscript.sql in the default directory. Once a script has been created you will get a button Show Script at the bottom of the window. Clicking on this will show you the script file.

The script is generated when you either run the load or use Preview. Using Preview gives you the ability to generate the load script without running the load: you may then wish to save the load script for running at a later time.

If running a large load it is recommended that you do not use Generate Script - this will slow the load.

Use Bulk Inserts

When the Use Bulk Inserts option is specified, AQT will load multiple rows with a single SQL statement.Example:

insert into table (col1, col2, col3) values ('A',1,'a'), ('B',2,'b'), ('C',3,'c'), ('D',4,'d')

You can specify the maximum number of rows that will be inserted per Insert statement (Max Insert Rows).

This provides a faster method than Load using Inserts and can sometimes even be faster that the parameterized method.

Some databases do support this syntax so for these databases this load method cannot be used.

Disadvantage of Bulk Inserts

You need to be aware of the following issue with using this method.

If any value in the Insert statement has an error, the entire Insert statement is rejected by the database and none of the rows in that statement will be loaded.

So, for instance, you are loading 50 rows per Insert statement, any error in those 50 rows will cause all 50 rows to not load.

Consequently this method is most suitable for loading data which is "clean" and likely to load without errors.

Error Limits with Bulk Inserts

The Data Loader willl stop when either the Duplicate Row Limit or Error Limit have been exceeded.

When using the Bulk Insert option, these limits apply to the number of statements which hit this error, rather than the number of rows which did not load.

For example: suppose you are loading 50 rows per Insert statement and the Error Limit is 20. If a single Insert statement fails then 50 rows will not be loaded, however this will only count as 1 towards the Error Limit. The Error Limit will only be hit when 20 Insert statements have hit this error. In this case up to 1000 rows will not have been loaded.

Load options

On the Options Tab, you can specify a number of options relating to the load.

Load Method

The options here are discussed in Load Method

General Options

• Start From Row. The record in the source that the load is to commence from.

• Maximum Rows to Process. The number of rows that AQT is to load before stopping.

• Remove trailing spaces from string values. When this option is selected, AQT will remove spaces from the end of end of string values. This will be done for all character columns.

  If you only want this done for some columns:

  • de-select this option

  • on the Mapping tab, use the function rtrim on the columns you want the values trimmed

• Treat zero-length values as NULL. see the discussion below

• Use TO_DATE function with Dates. This option will appear when you are loading data into an Oracle table. If selected:

  • AQT will use the TO_DATE function when loading data into Date columns. This is needed if your date values have a date plus time component.

  • AQT will use the TO_TIMESTAMP function when loading data into Timestamp columns

• Ignore blank lines when loading from Files or Excel Files.When this option is checked, AQT will ignore any blank lines in your input file.

• Ignore lines where. This allows you to specify conditions whereby the input data will be ignored by the Data Loader. This can be used to ignore junk data from an input file, or to only load a specific set of data. This is discussed in more detail later.

• Delete table contents before loading. When this option is selected, the existing rows in the table will be deleted before the load is started.

  For Oracle, Sybase and SQL Server, AQT will use the Truncate Table command; for other databases, AQT will use Delete from Table.

  This option cannot be specified when the load is running in Update mode.

• Replace commas with fullstops for numeric values. This option is useful for loading numeric values which have the comma as the decimal separator. Most databases require that a fullstop / period is used as the decimal separator. Selecting this option provides a simple way of replacing commas with fullstops for the data being loaded into numeric columns.

  This can also be achieved by coding the mapping function rep([comma],[dot]) for all numeric columns.

• Remove commas from decimal values.This option is useful if your decimal values are using a comma as a thousand separator. Most databases do not recognize thousand separators and will give an error when these are specified.

  This can also be achieved by coding the mapping function rep([comma],) for all numeric columns.

• Truncate char columns to col length. When this option is specified, values for character columns will be truncated to the defined length of the column.

  This option should be used with caution - when it is specified you will loose some data. However it can be useful if you are loading "unclean" data and prefer a truncated row to be loaded, rather than the row being rejected.

  When Report on this is selected, AQT will write to message to the Report file every time a value is truncated.

Treat Zero-length Values as NULL

This option is useful when loading data from a File or an Excel File. Often a zero length (or missing) value is used to represent Null. When this option is selected, AQT will load such values as Null. For instance the following line in a csv file:

SMITH,,0,,FRED

will be loaded as:

SMITH,null,0,null,FRED

When this option is specified, this is done for all columns being loaded. If you want this to be done for some (but not all), columns, you can:

• de-select this option

• for every column you want blanks to be treated as nulls, specify the nib function (null-if-blank) in the Load Spec (eg <1:nib>).

This option is only in effect when data is loaded from a file or Excel file. When the data is loaded from a table or query, zero-lengths values in the source table/query will be loaded as zero-length values in the target table. If these need to be loaded as nulls, you should use the nib function as discussed above.

Note that this option can equally be acheived using Column Functions - a Column Type of char and a Function of nib will also do this.

If you are loading into an Oracle Not Null column you may wish to use the function sin (space if null) instead of this. This will load a single space when the input value is Null or a zero-length string.

Ignore lines where

You can use this to specify a number of conditions. When any condition is satisfied, the input data is ignored.

The format of a condition is:

value1:condition:value2

For both value1 and value2 you can refer to the columns of the input data, as per mapping specs. If value2 is not specified, it is taken as blank.

You can have multiple conditions, separated by a comma

Examples:

Values condition can take are as follows:

Notes:

• When using numeric conditions (lt, le, ge, ne) it is recommended that the num function is used to convert numeric values to a consistent format. If this is not done, and the values contain a thousand separator, the comparison will not work correctly.

  Example: <2:num>:lt:10

• String comparisons (eq, ne and co) are case independent.

Commit Frequency

This specified how frequently AQT is to commit the inserted/updated rows to the database.

Notes:

• MS Access only has partial support of transactions. If you select an option other than Every Row, you can get the error message:

  Unable to start a transaction: [ ] [ ] Attribute cannot be set now.

  The resolution to this is to use a Commit Frequency of Every Row.

• When Every Row is selected, the Data Loader will do no commits. Normally this means there will be a commit after every row inserted/updated. However if either:

  • you have de-selected Options > Technical > Auto Commit or

  • you are running within a transaction

  then the load will be done within a transaction. After the load has completed you will need to do a Commit or Rollback to commit or rollback the changes.

Create Table Options

These options are described in Create new table

Column Functions

This provides a mechanism to apply a particular mapping function to all columns of a particular type. This is a big time saver if you have many columns you need to apply a mapping function to.

In Column Type enter the name of a column type. You can use a partial type. For instance CHAR will apply to both VARCHAR and CHARACTER. If you prefix the column type with an * it must be an exact match (eg. *CHAR only matches a column type of CHAR). Column Type can be all for it to apply to all values irrespective of the data type.

In Function enter the mapping function, as described in Map to target from Source.

Examples

Error-handling options

The Data Loader has comprehensive options for handling errors during the load process. Errors are split into two types, as you may wish to have different options for handling each:

For both of these error types, you can specify the action to take when the errors are encountered:

• whether the Data Loader is to Continue processing or is to stop

• after how many errors the processing is to stop. This allows you to specify an acceptable number of errors.

• whether AQT is to Commit or Rollback after getting these errors. This option will be available only if the Commit frequency is set to Once finished or Every xx rows. Do not use these two options for MS Access tables, which do not fully support commit.

When any errors occur they are written to the Error Report file. Any records that fail to be loaded are written to the Duplicate Row and Error Rows files. These records are written in csv format (comma delimited, with strings delimited with double-quotes) irrespective of the source of the data.

Run the load

Load Data

This button begins the load.The progress of the load will be reported on the status-bar at the bottom of the window, and a popup message will be given when the load completed. If you get any errors loading the data, you can see these with the Options tab > Error Handling > Write Error Report to > View.

Preview

The Preview of your load shows you the data to be loaded into your table, and does not run the load. Any obvious errors, such as non-numeric values being loaded into numeric columns, or string values which are too long, are flagged.

Cancel

This button becomes active when you are running the load, and enables you to stop the load.

Create new table

The Data Loader has an option to create the table being loaded. You select this by checking the Create new table checkbox (which is on the right of the table name).

When this option is selected, AQT will create the target table at the start of the load process.

Definition of the target Table

The left grid on the mapping tab shows the details of the table which will be created and loaded.

• When you are loading from either a table or query AQT will get the column definitions from the table / query you are loading from.

  If you are loading from a table/query in a different database type, AQT will convert the column definitions between the two database types. This work well in most cases but is not perfect; if you wish to map data types in a particular way you can the Type Mapping option (described later).

• When the loading from either a file or Excel worksheet, AQT will get the column names and column types by parsing the file.

  AQT will do this automatically when the table is being loaded. However an alternative is for you to click on the Scan button. When you do this, the file will be parsed and the column name / type and null-indicator displayed in the grid.

In both cases, you will have the option to amend the column names / types to more suitable values as required.

Options

There are a number of options (on the Options tab) which are relevant when using Create new table mode:

• Review SQL before creating. When this is checked, you have a chance the review (and amend) the Create Table statement before it is run.

• Drop Table before creating. When this is checked, AQT will drop and create the table.

• Tablespace. The tablespace the table will be created in. Only used for Oracle and DB2.

• Database. The database the table will be created in. Only used for DB2 z/OS. When this is specified the In Database xxxx clause will be used in the Create Table statement.

• Type Mapping. This option allows you to specify how data types are mapping between the source database and the target database. Specify this in the following format: from_type->to_type,from_type->to_type.

  Example: timestamp->date,time->char(8).

  In this example timestamp columns in the source table will be created as date columns in the target, and time columns will be created as char(8). If the length is omitted from the char specification, it will be taken from the length of the source column.

• # rows to scan. This specifies the number of records in the source file / Excel worksheet to scan to determine the data types of the columns. By default this is 1000 records for files or 100 rows for an Excel file. Specify 0 for AQT to parse the entire file.

Update Mode

The Create new table option cannot be specified when the load is running in Update mode.

More info on Load

Refresh Target Tab

If the Target table definition is changed, the Refresh button (top of Data Loader window) will reload the columns in the Target tab.

Type Conversion

Nothing special needs to be done in order to do type conversion of data (for instance, from string to numeric values). When loading data, AQT supplies all column data as strings. The ODBC driver (which does the low-level interface to the database), will convert the string values to the appropriate data type. As long as the data values are valid (eg. all numeric values are indeed numeric) then type conversion will happen automatically.

Binary, LOB and Image Columns

The Data Loader will load binary columns, and large columns such as BLOBs and CLOBs. There is no limit on the size of a LOB column being loaded. This can be used to transfer LOB data between databases of different types.

If you are using the Load using insert statements method, AQT will load the LOBs by using the parameterised insert method (as described in Inserting BLOBs or CLOBs). This saved SQL script will include these parameterised inserts and can be run as an SQL script through the Run SQL window (though you will have to do this prior to your AQT session ending, otherwise AQT will purge the LOB files).

Load speed

The Data Loader is written to run fast. However if you have a very large table then it will be quicker to use the native utilities of your database, rather than the AQT Data Loader.

If you switch on Options > Debug Mode, timing information on the Data Loader will be written to the debug file. This can help understand the performance of the loader.

Save/Open Load Specifications

Once you have set up the options for loading a table, you can SAVE these options with File > Save Load Options. This will save to file the specifications for this load. If you later use File > Open Load Options, the load specification will be opened from a disk file and set up in the Load data window.

Batch mode

You can also run the Data Loader in batch mode. This is done by specifying an --aqt load batch command. When you do File > Save Load Options or File > View Load Options, this will generate an --aqt load batch command that you can use in your batch file. This batch command can also be used in the Run SQL window.

See Scripting - Load for more on this.

Loading Multiple Tables

If you wish to load multiple tables, you need to run multiple --aqt load statements. You can use the Generate Text utility to generate these --aqt load statements. The procedure for doing this is described in more detail in Scripting - Load.

Load-Update with MySQL

When you run an Update in MySQL. it returns the message Number of rows affected=0 if the data has not changed. For instance if you run:

UPDATE Customer_Details SET customer_type ='T' WHERE cust_code = '000001'

you will get Number of rows affected=1

If you run the same statement a second time, you will get Number of rows affected=0. This is because the second time this is run, no data is actually changed so MySQL return a row count of zero.

This is different behaviour from all other databases, which will always return a row count of 1 for this statement. A row count of zero is used to indicate that no data has been found (by the WHERE clause).

This causes a problem with Load-Update; when it sees a row count of zero, it assumes that the row-update has failed to find the row, so gives an error. If you are using the default options, the load will then terminate after 20 such errors.

You can get around this problem by specifying (on the Errors tab) Other Errors > Continue.

Load problems

Problems when Source and Target are in the same database

see Data Loader error with MS SQL Server and Sybase

Unable to start transaction

see MS Access: load unable to start

Excel Dates

MS Excel appears to have a bug with dates prior to 1 Mar 1900. As Excel incorrectly thinks that 29 Jan 1900 is a leap day, all dates prior to this are one day out of sync between how they appear in Excel and how they appear in AQT. If you are loading date values from an Excel spreadsheet into a table, you may notice this problem.

DB2 Long Varchar for Bit Data

You may run into problems loading data into these columns. When running the load, the ODBC driver misreports the datatypes of these columns. We have no resolution to this.

Use of Parameter Markers

The Data Loader makes extensive use of parameter markers. However, some ODBC Drivers cannot process parameter markers successfully. For these databases, you can specify the Load using Insert statements option. This is not as fast as the parameter-marker method, however for some databases it provides a more reliable method for loading data.

Oracle and DB2 LOBs

Both Oracle and DB2 cannot load zero-length LOBs. The ODBC Driver hangs or crashes when this is attempted. To circumvent this, for these databases AQT will load a single blank into the LOB.

Loading Dates from Excel

Excel dates often come across to AQT in dd/mm/yyyy format, which won't load into most databases. The solution to this is to use the date function to convert the date to yyyy-mm-dd format. See Map to target from Source for information on the date function.

Loading Large Character Columns in MS Access

By default, AQT loads data by using parameter markers. MS Access has a number of limitations with the use of parameters. As a result, if you have character columns larger than 100 bytes, the load will fail with message String data, right truncated.

The solution is to load using the Load using Insert statements option.

Loading Dates into MS Access

When loading a date into MS Access, you may get the message:

22005;Invalid character value for cast specification (null)

This error is quite misleading; the cause of the problem is the date is not in the correct format.

Loading Numeric Values into Oracle when the Decimal Separator is a Comma

Many countries use a decimal separator of comma. A numeric value will be displayed as 1234,56. However the period must still be used when loading the numeric value. For instance, you must specify:

insert into numeric_table values(1234.56)

even when your decimal separator is a comma. If you use values(1234,56) you will get a syntax error.

As a result, the Data Loader will always specify numeric values with a period rather than a comma.

The only place this doesn't work is with Oracle when your NLS_NUMERIC_CHARACTERS is set to ',.'. This specifies to Oracle that the decimal separator is comma, and in this case Oracle will give an error when AQT passes a numeric value with a period.

There are two ways to get around this:

• use the Load Using Inserts option

• run the following Oracle command prior to running the load:

  Alter session set NLS_NUMERIC_CHARACTERS='.,'

Loading Oracle Numeric columns

In Oracle, columns can be defined as NUMERIC without a precision or scale specified. While this is very flexibile from a data point of view, it creates a problem with the way these columns interface to AQT. Such columns appear to AQT as having a scale and precision of zero. While AQT has a workaround to deal with this, the decimal digits are truncated. This appears to be a limitation of the ODBC Driver.

The only workaround is to load your data you Load using Inserts statements.