SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Examples of Using import to Load an SQL/MX Table
Example 1: Importing Data From a Delimited File
This example is of an import of data from a delimited file. It shows the structure of the table to be
loaded, the data to be loaded (the input data file), the format file describing the data, and the
import command used to load it.
Table Structure
This statement creates the target table, COMPANY:
CREATE TABLE company
( id INT NOT NULL
,company VARCHAR (176)
,phone VARCHAR (12)
,fax VARCHAR (12)
,PRIMARY KEY (id)
);
Data to Input
The input file, COINPUT, contains records like this:
1,"Test String 1","111-222-3333","222-333-4444"
2,"Test String 2","111-222-3333","222-333-4444"
3,"Test String 3","111-222-3333","222-333-4444"
4,"Test String 4","111-222-3333","222-333-4444"
5,"Test String 5","111-222-3333","222-333-4444"
6,"Test String 6","111-222-3333","222-333-4444"
7,"Test String 7","111-222-3333","222-333-4444"
8,"Test String 8","111-222-3333","222-333-4444"
Format File Describing the Data
Create a format file, FORMFILE1, which consists of [DATE FORMAT], [COLUMN FORMAT], and
[DELIMITED FORMAT] sections:
[DATE FORMAT]
DateOrder=MDY
DateDelimiter=/
TimeDelimiter=:
FourDigitYear=Y
DecimalSymbol=.
[COLUMN FORMAT]
col=id,N
col=company,N
col=phone,N
col=fax,N
[DELIMITED FORMAT]
FieldDelimiter=,
RowDelimiter=\n
Qualifier="
import Load Command
This import command imports data into the target table COMPANY from the delimited input file
COINPUT using the format file FORMFILE1:
import cat.sch.company -I coinput -U formfile1 -W DELIM
Example 2: Importing Data From a Fixed Width File
This example of an import from a fixed width file shows the data to be loaded (the input data file),
the format file describing the data, and the import command used to load it.
202 Reorganizing SQL/MX Tables and Maintaining Data