In fixed-length format import files, data records have a fixed length and the data fields inside the records have a fixed position and length.
Example: Control file 1
This example uses multiple columns in fixed-width field:
OPTIONS(ARRAYSIZE=3) LOAD INFILE 'test1.dat' INTO TABLE SLTEST ( "NAME" POSITION(1-5), ADDRESS POSITION(6:10), ID POSITION(11-15) )
Example: Control file 2
OPTIONS (SKIP = 10, ERRORS = 5) -- Skip the first ten records. Stop if -- error count reaches five. LOAD DATA INFILE 'sample.dat' -- import file is named sample.dat INTO TABLE TEST1 ( IDINTEGER POSITION(1-5), ANOTHER_ID INTEGER POSITION(8-15), DATE1 POSITION(20:29) DATE 'YYYY-MM-DD', DATE2 POSITION(40:49) DATE 'YYYY-MM-DD' NULLIF NULL)
Loading variable-length records
This section contains examples of the control file when loading data from a variable-length import file:
Example: Control file 3
This example uses multiple columns that have separators instead of fixed-length fields.
LOAD INFILE 'test1.dat' INTO TABLE SLTEST FIELDS TERMINATED BY ',' ( NAME, ADDRESS, ID )
Example: Control file 4
LOAD DATA INFILE 'EXAMP2.DAT' INTO TABLE SUPPLIERS FIELDS TERMINATED BY ',' (NAME VARCHAR, ADDRESS VARCHAR, ID INTEGER) -- EXAMPLE 2 OPTIONS (SKIP=10, ERRORS=5) -- Skip the first ten records. Stop if -- error count reaches five. LOAD DATE 'YYYY-MM-DD HH:NN:SS' -- The date format in the import file INFILE 'sample.dat' -- The import file INTO TABLE TEST1 -- data is inserted into table named TEST1 FIELDS TERMINATED BY X'2C' -- Field terminator is HEX ',' == 2C -- This line could also be: -- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '[' AND ')' -- Fields can be enclosed -- with '[' and ')' ( ID INTEGER, ANOTHER_ID DECIMAL(2), DATE1 DATE(20) DATE 'YYYY-MM-DD HH:NN:SS', DATE2 NULLIF NULL ) -- ID is inserted as integer -- ANOTHER_ID is a decimal number with 2 -- digits. -- DATE1 is inserted using the date string -- shown -- The default date string is used for DATE2. -- If the column for DATE2 is 'NULL' a NULL is -- inserted.
Running a sample load by using solidDB Speed Loader
The solidDB package contains a sample that demonstrates how to use solload to load files, see solidDB tools sample.