Guide: Import Text File to IBM i Database Table with CoolSpools

Importing from Text Files

Ariadne Software are pleased to announce the addition of new commands to the CoolSpools suite that allow the import of data from text files to IBM i (AS/400, iSeries) database tables. These commands allow import from both delimited and fixed position text files, and provide additional functionality that is not available with IBM i system command CPYFRMIMPF, such as the following:

• Create a new database file with field definitions derived from the text file content.
• Automatically assign field names from column headers.
• Specify which columns of data to import, and the order in which they should appear in the database file record.
• Select which rows to import by applying selection criteria based on the row data.
• Preserve data within a delimited text field that contains a carriage return, and do not treat it as two incomplete rows.
• Specify the required action in the event of a data error being encountered (*STOP, *CONTINUE, or write any records in error to an error file).
• Specify whether truncation of character fields or rounding of numeric fields is permitted.

IMPCSVDBF Import from a Delimited Text File

The command IMPCSVDBF allows you to import data from a delimited text file (e.g. comma separated, pipe delimited). Data can be imported to an existing database table, or you can create a new database table with field definitions (length and data type) and field names assigned based on the text file content.

By default all data columns are imported in the order that they appear in the text file row, but you have the option to include or exclude specific columns, and to specify the order in which columns are to be imported to the database record.

There are multiple options for assigning field names when creating a new database table. Field names can be automatically assigned based on column headings, or you can assign field names based on the order of the columns in the text file (e.g. COL1, COL2) or the order of the fields in the database record (e.g. FLD1, FLD2). You can also assign a field alias, which allows a longer value than the field name, up to 128 characters.

There are two methods by which rows can be selected. Use the FROMROW() and TOROW() parameters to import a range of rows, or use the SLTROW() parameter to apply selection rules based on the content of the row, such as SLTROW((COL1 *EQ ‘ABC123’)) to select only rows where the first column is equal to ‘ABC123’.

IMPFXDDBF Import from a Fixed Position Text File

The command IMPFXDDBF allows you to import data from a fixed position text file. Data can be imported to an existing database table, or you can create a new database table, specifying the field names and attributes within the command parameters.

Only those data columns whose positions are defined within the COLUMNS() parameter are imported. Use the COLUMNS() parameter to provide the start position, length, data type and name of each column, or specify *NEXT as the start position of a column to start immediately after the end of the previous column. If you want to define a data column for use in row selection, but do not want to load it to the database file then specify special data type *SLTONLY.

There are two methods by which rows can be selected. Use the FROMROW() and TOROW() parameters to import a range of rows, or use the SLTROW() parameter to apply selection rules based on the content of the row, such as SLTROW((RCDTYP *EQ ’01’)) to select only rows where the content of a column defined as RCDTYP in the COLUMNS() parameter is equal to ’01’. This allows you to easily import data from a multi-format text file with different types of row identified by a record type.

Requirements

The new commands are available in the CoolSpools Version 7 Database Module from fix pack level 064 and upwards. If you already have CoolSpools Version 7 installed, you can update to the latest fix pack level by following the instructions in the Maintenance Guide. If you are running CoolSpools Version 6 or older, then it is free to upgrade to CoolSpools Version 7 if your Support and Maintenance is current.

Examples

Please see the PDF document at the link below for further details, including examples of both commands:
Import Text File to DBF with CoolSpools