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

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

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.

When this option is selected AQT will also use the TO_TIMESTAMP function for 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.

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.

Generate Script

This is discussed in Load method

Replace commas with fullstops for numeric values

This is discussed below.

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.

Commit Frequency

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

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.

Note: 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:

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

Replace commas will fullstops when 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.

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).

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.