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:

<1>:eq:N/A	Ignore lines where the first column is N/A
<3:num>:lt:10	Ignore lines where the value in column 3 is less than 10
<1>:eq,<1>:co:null	Ignore lines where column 1 is blank, or column 1 contains the string "null".

Values condition can take are as follows:

eq	Equal to
ne	Not equal to
co	Contains. True if value1 contains string value2. Example: <2>:co:XX
lt	Less than
le	Less or equal to
ge	Greater or equal to
ne	Not equal
neq	Numeric equal. This will return true if the values are numerically equal. Example: 12.00:neq:12 will return True
nne	Numeric not equal

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

Column Type	Function	Comments
date	date(d)	Applies the date(d) function to all date and datetime columns
*date	ifnull(1900-01-01)	Will load 1900-01-01 into all date columns if the input is Null.
char	lcase	Will load all character values as lower case
int	ifnull(0)	WIll load 0 into all integer, smallint, bigint column if the input is Null
date	rep([dot],-):date(d)	Will load date values when a period is used as field separator.
all	nullif(-)	Will load nulls if the value is - (for all column types)

Advanced Query Tool

https://www.querytool.com