Utilities 7-27
How to Create a Command File
Fields in the input file are padded with blanks (represented by + in the
following example) to create data rows in which the locations of data fields
andthe number of characters are the same across all rows. The definitions for
these fields areCHAR(15), CHAR(2), CHAR(5), and CHAR(12), respectively.
Foryour reference, the character positions and five example data rows from
thecust_loc_data file are displayed:
1234567890123456789012345678901234
Sunnyvale++++++CA94086408-789-8075
Denver+++++++++CO80219303-936-7731
Blue Island++++NY60406312-944-5691
Brighton+++++++MA02135617-232-4159
Tempe++++++++++AZ85253xxx-xxx-xxxx
The following example of a dbload command file illustrates the character-
positionform of the FILE and INSERT statements. The example includes two
newfiles, cust_address and cust_sort, to receive the data. For the purpose of
this example,cust_address contains four columns, the second of which is
omitted from the column list. Thecust_sort table contains two columns.
FILE cust_loc_data
(city 1-15,
state 16-17,
area_cd 23-25 NULL = "xxx",
phone 23-34 NULL = "xxx-xxx-xxxx",
zip 18-22,
state_area 16-17 : 23-25);
INSERT INTO cust_address (col1, col3, col4)
VALUES (city, state, zip);
INSERT INTO cust_sort
VALUES (area_cd, zip);
The syntax for the character-positionFILE statement can be represented as
follows:
: start -
end
);
FILE filename
,
fieldn start
NULL =
"null string"
(-