BINILOAD can load data from CSV files, generated by BOUTLOAD or created by any tools, according to the CSV file format rules.
An input file in CSV format generated by BOUTLOAD may contain lines with the following information:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The line (1) may contain the name of the BOUTLOAD utility routine, the corresponding utility routine’s output format version, and the date and time of creation of the CSV output.
The next lines (2) – (6) contain database name, record name and record reference. If realm name was specified, the header also contains realm name and realm reference.
Lines (1)-(6) are optional in CSV file while loading by BINILOAD and ignored.
The header line (7) and the content line (8) are mandatory, but control fields with name FIELDS and RECORD are optional. If control field FIELDS is present, then control field RECORDS must be present as well, and vice versa.
The header line (7) and lines (1) - (6) can be in any order in the beginning of CSV file, but not be mixed with content lines.
The header line should contain name of fields according to the following naming rules:
- “DB Key Ref” and “DB Key RSQ” – field names for the database key of the record
These fields are optional.
If "DB Key Ref" is not present, BINILOAD will then use the record reference number (REC-REF) of the record type specified for STORE RECORD.
If both "DB Key Ref" and "DB Key RSQ" are not present, database key values are to be assigned in the same sequence as the order of records in the CSV file (see section "Assigning the database key value to a record" in chapter "Statements for BINILOAD").
- “Member set-name” – field name for a one-byte long item with the content
Y = Member inserted into the SYSTEM set set-name
N = Member not inserted into the SYSTEM set set-name
This field is mandatory for all singular sets specified in a INSERT statement.
- “Owner DB Key Ref set-name” and “Owner DB Key RSQ set-name” - field names for the database key of the owner in set-name.
For each regular set set-name specified in a INSERT statement the field "Owner DB Kef Ref set-name" is optional, the field "Owner DB Kef RSQ set-name" is mandatory.
If "Owner DB Kef Ref set-name" is not present, BINILOAD will then use the record reference number (REC-REF) of the owner record type of the set specified in the INSERT statement.
(see section "Format 3: Using the database key to define the owner" in chapter "Statements for BINILOAD").
- The item names according to the user schema;
If an item name of the schema is omitted, a default value is used for it. This default value is blank X'40' for alphanumeric fields, unicode blanks X'0020' for national fields, low value for DBKEY fields and 0 in the respective numeric format (unpacked, packed or binary). - Area ref - in the case of record type which is distributed to realms if its records are copied from multiple realms. This field is ignored in BINILOAD.
A semicolon (";") is used to separate the individual values.
Values in content line (8) must correspond to the order of fields in the header line (7).
Fields in CSV file can be in arbitrary order.
The correct order of fields is determined based on fields’ names in the header line.
Alphanumeric values in CSV can be enclosed in double quotes (") to represent special characters like semicolon (;). If a double quote character is contained in a value, which is enclosed in double quotes, it must be duplicated.
The items’ contents are converted to the type defined for these items in the Schema DDL and are stored in the database in the correct format.
In case if item content can’t be converted to the item type due to incorrect value in CSV file, error 908 will be output, the current record will not be stored and BINILOAD will continue to process with the next line in the CSV file.
If a field of DB record is missed in CSV file, then BINILOAD will fill the field by default value (0, X’40’ or X'0020' depending on data type).
If there is a field in CSV, which doesn’t exist in DB record type, and doesn’t correspond any field of system information part (like ‘DB Key Ref’ , ‘DB Key RSQ, ‘DB Key Ref set-name’, ‘DB Key RSQ set-name’, ‘Member set-name’, then BINILOAD will reject loading.
The input file may be created by BOUTLOAD or by any tool. The input CSV file can be a SAM or ISAM file (EDT format) containing records of different length in variable record format (RECFORM=V).
The name of the input file is specified in the INPUT-FILE statement.
For loading data from an input file in CSV format, the DBCOM must be available.
At the end of this chapter there is an example for the BINILOAD command sequence for loading data from an input file in CSV format.