DDL Command LOAD

DDL Command LOAD The LOAD Command

Load records from an external file into the specified Table of the data base. The "filename" tells which file to load, "tablename" says where the data goes, and "rdname" is the name of a description telling us what the records in the external file look like.

Notation standards

Options

Answers

Syntax:

LOAD/optn [cx] tablename rdname [(AIname)] filename [skip count] [ERROR=collection]

Options:

B - This file contains binary data and/or is blocked;
C - Count option. Load the first "n" records only. See Options;
F - Blank fill ascii fields that contain the null character;
I - Use tab settings from MODE TABSET;
N - Do not discard null lines (zero line lengths);
O - MEMO fields in the input file are pure text rather than SGML;
R - Reverse byte order of binary data types;
T - If DOS, load UNIX format data, if UNIX, load DOS format data;
X - Suppress the command response;
Y - Do not alter the command answer cells;

Answers:
         Ctl.Ans1=Number of records loaded;
         Ctl.Ans2=Zero;
         Ctl.Ans3=Zero;

Notes:
The filename can be a full path filename refering to any input device.

Record Descriptions, refered to by the "rdname," describe what the data records in the external file look like. Record Descriptions (RD) are defined in the Application Interface (AI). If the RD that you desire to use for a particular LOAD is defined in the currently open AI, then simply refer to its name in the LOAD command syntax. If the RD is defined in an AI that is not the currently open one, then you can qualify the rdname with the AI name in which it is defined. The qualifying AI name must be enclosed in parenthesis in the command syntax. For example:

LOAD CUSTOMERS CUS_REC (MAINT_AI) PEOPLE.DAT

The records in the file PEOPLE.DAT are added to the CUSTOMERS table. The record description CUS_REC which describes the data in PEOPLE.DAT, and is therefore controlling the load, is not found in the currently open AI, but is found in the AI named MAINT_AI.

If a collection is specified, at the completion of the LOAD the collection has the subset best expressed as "Find me the records that were just loaded."

If the C option is used, the LOAD stops after the number of records specified by the count have been loaded.

The optional skip count specifies how many records to skip at the beginning of the file before the load commences.

Index structures are not updated for new records added by the LOAD command. It is your responsibility to use either the CONSTRUCT or STRUCTURE commands to update structure after a LOAD.

File Formats

Three input file formats are specified via the options:

If neither the T or B options are present, then the input file is SDF
format, that is, each ASCII line is terminated with CR LF (carriage
return, line feed).

If this is a DOS or Windows version and the T option is present, then
the file is UNIX format, that is, each ASCII line is terminated with LF
(line feed). If this is a UNIX version then the T option is to load a DOS
format where each line is terminated with CR LF (carriage return,
line feed).

If the B option is used, then the file contains binary data and/or is
blocked. There are no line termination characters. Each record is N
characters in length, where N is the number of characters specified
in the record description. Note that if binary (integer or floating point)
data is present in the input file, you mustuse the B option. Any non
binary data is ASCII.

In addition, there are four standard file formats that can be specified by the record description:

DBF -dBase II, III, or III+ format (For more information
DBF4 -dBase IV format abount record descriptions
DIF -Data Interchange Format see Application Interface or
Delimited ASCII File Import and Export .)

The "Quick-and-Dirty" Record Description

Basically, record descriptions tell us what the incoming data looks like. If the data to load is in the exact same format as the Table description, then an asterisk (*) can be used in place of the rdname. Of course, you know for sure that it will be in the same format if the file to load was created by an UNLOAD command that had used the asterisk (*) to describe its output file, and you have not changed the Table definition since unloading it.

The ERROR clause

If the ERROR=collection clause is specified, then any records which contained bad dates or had truncation errors will be isolated in the error collection at the completion of the load. The error collection must be different from the main collection specified in the LOAD. So the error collection, if specified, contains at the completion of the load the subset isolation best expressed as "Find me all the records for which errors were detected during the load."

Examples:
LOAD A CUSTOMERS CUS_REC CUST.DAT ERROR=B

The records in the external file CUST.DAT, all of which are as described by the record description CUS_REC are added to the CUSTOMERS Table in the data base. All of the records of the input file were loaded into CUSTOMERS since there was no count option or skip count specified.

At the end of the load, collection A has the subset that is all of the records that were just loaded, and collection B (in the CUSTOMERS portion of B) contains any record(s) for which errors were detected, else it is zero.

LOAD A CUSTOMERS CUS_REC CUST.DAT 10 ERROR=B

Same as above except the first 10 records of the input file CUST.DAT were skipped before the LOADing commenced.

LOAD/C 50 A CUSTOMERS CUS_REC CUST.DAT 10 ERROR=B

Same, except that only records 11 through 60 of the input file were loaded.

Loading MEMO Fields

When you UNLOAD a Table that contains a MEMO field (or fields), and the Record Description that controlled that UNLOAD included the MEMO field(s),
then two parallel files are created. The first file contains the fixed portion of each record and is named as you specified in the UNLOAD command, the second file contains the variable-length MEMO field data and is named the same as specified in the UNLOAD command but with a .TTX extension instead of whatever extension you may have specified.

For example, if an UNLOAD involving MEMO fields had specified a file named CUSTDATA.DAT, two files are created, CUSTDATA.DAT and CUSTDATA.TTX.
There are pointers in each record of the first file that locate the memo field data for that record in the second file.

So, to LOAD with an RD that includes a MEMO field data-type both files must be present in the same folder in order to achieve a successful load.

For DBF files that involve MEMO fields the second file is named with a .DBT extension. When you load DBF files that include memo fields the rules are the same, that is, both files must be present in the same folder.

If you are transferring data that contains MEMO fields from a D The Data Language data base into a Thunderbolt data base, be sure to use the O option on the LOAD as the memo field data is pure text in D's .TTX file, not SGML text (which is the default for Thunderbolt).