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
EXAMPLE 1 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)
Example: 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
EXAMPLE 1 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 may 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 -- given above -- The default date string is used for DATE2. -- If the column for DATE2 is 'NULL' a NULL is -- inserted.
Running a sample load using solidDB® Speed Loader (solload)
The solidDB® package contains a sample that demonstrates how to use solload to load files. The sample is available in the samples/importexport/solload directory in your solidDB® installation directory.
The sample loads data into a table called TEST1. There are two control files:
▪delim.ctr uses delimited fields
▪fixed.ctr uses fixed length fields
1 Start the solidDB® server.
2 Create a sample table by using the load.sql script and solidDB® SQL Editor (solsql).
3 Start loading data into the database.
To use the delimited fields control file, enter the following command:
solload "tcpip 1964" dba dba delim.ctr
To use the fixed-length control file, enter the following command:
solload "tcpip 1964" dba dba fixed.ctr
The user name and password are assumed to be dba.
4 Verify that the load succeeded. The output of a successful load using delim.ctr or fixed.ctr is: