Previous Topic

Next Topic

Book Contents

Book Index

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

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:

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:

Commit Frequency

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

Notes:

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)