Table Declaration - File Structure

This can be done manually or you can use the Table Declaration wizard to aid you in setting up the script.

The file will contain entries of the following structure:

LOCK_AND_LOAD
BEGIN TABLE_DECLARATION
FORMAT = Format of the file supplied
DELIMITER = Delimiter used within the file format
SCRIPTFILE = name of file containing the table load script
DATFILE = name of source file
DATFILELIST = Name of file containing the list of source files
DATE = date associated with data file
TYPE = type of load operation to perform
QUALIFIER = Qualifier used within the file
WIDTH = Record width of flat file or number of fields in delimited files
SKIPROWS = Specifies number of rows to omit
TRAILERSIZE = Sets the number of characters to be ignored at the end of a file
END TABLE_DECLARATION

The values of the fields are as follows:

LOCK_AND_LOAD

The LOCK_AND_LOAD function must be entered before the Table Declaration as it must be implemented before you begin loading the tables. The values of the fields required in the Table Definition can be specified in the Table Declaration wizard.

FLAT

Used for files with fixed length records. When loading Unicode data the UnicodeFLAT option must be used.

DELIMITED

Used for files which have been delimited by a particular character and therefore may have records of varying lengths. When loading Unicode data the UnicodeDELIMITED option must be used.

BESPOKE

Forces the optional iLoader Tools injected DLL to process data from various non-standard sources.

The format of the BESPOKE option is as follows:

<Type> is used to indicate to the Loader Tools module the bespoke import mode. The variable parameter list is passed as a string to the tool module. If this value is not declared for a table, the default value of DELIMITED is used.

DELIMITER

This field will only be available when using a delimited file. The values for this field are:

COMMA - Indicates that the file has been supplied in a delimited format and the comma character (HEX 2C) has been used as the delimiter.

TAB - Indicates the file has been supplied in a delimited format and the tab character (HEX 9) has been used as the delimiter.

PIPE - Indicates the file has been supplied in a delimited format and the pipe character (HEX7C) has been used as the delimiter.

If there is no delimiter supplied for a table, and the format of the file has been defined as DELIMITED, then the default value of PIPE will be used.

SCRIPTFILE

Specifies the name of the script file that defines the layout and structure of the data.

DATFILE

This is the name of the data file to be loaded.

If more than one data file is to be loaded into a single table, then use the name DATFILELIST. This must be a file in the Data Repository directory that contains a list of the data files to load. These files must also be in the Data Repository and the list should include the file name and path. If no path is specified, then the path will be configured to the data load repository path. Note: Up to 300 files can be added to the datfilelist.

DATE

Allows the date at which the data extract was created to be recorded. The date must be supplied in the format DDMMCCYY.

TYPE

There are four possible keywords for this part of the Script File.

REFRESH

When a Refresh is performed, all data in the existing table is deleted and replaced with new data.

APPEND

When an Append is performed, all records in the import file are added to the end of the existing table.

UPDATE

When an Update is performed, it will use the key defined as the update key to determine which records in the database are to be updated. Any records in the update file that do not match an existing update key will be added to the end of the table.

UPDATE_ONLY

Updates records where the key matches and does not append additional records.

QUALIFIER

QUOTES

States that each of the fields within the record are delimited by double quotes (").

NONE

Indicates that there are no qualifiers around the fields in the record.

If there is no qualifier supplied for a table, and the format of the file has been defined as DELIMITED, then the default value of NONE will be used.

WIDTH

For fixed-width flat files, this should be the physical length of the records in the file. The length should include any carriage return (CR) and line feed (LF) characters, e.g. if the length of the data file is 125 but records are separated by CR/LF characters then the actual width is 127.

For delimited files, the width is the number of columns in each record.

Modifying Default Behavior

Control file declarations

SAMPLE = n

Loads only a sample of around n records for each table.

DO_NEWTABLESONLY

Will only execute loads events for tables that do not exist.

USES_CSV_DECODES

Decode files are expected to be in comma delimited format, rather than pipe.

The following declarations turn the processing of the main script files on or off. <Boolean> should be TRUE or FALSE. The default for each of these is TRUE.

SKIPROWS

Allows you to load data without loading the first row or rows, depending on what has been specified, although this is usually 1. This allows you to import text files without importing a header or any rows containing field names.

TRAILERSIZE

Sets the number of characters to be ignored at the end of a file. This only works for flat files.

• Editing Scripts
  • Using The iLoader Editing Tools
  • Using the Table Declaration Wizard
  • Table Declaration File Structure
  • Table Declaration Example
  • Using the Table Definition Wizard
  • Table Definition File Structure
  • Table Definition Example
  • Indexing Fields
  • Creating Scripts Manually