The control file syntax has the following characteristics:

The control file begins with the statement LOAD [DATA] followed by several statements that describe the data to be loaded. Only comments or the OPTIONS statement can optionally precede the LOAD [DATA] statement. For details of the syntax elements for the control file and the list of reserved words, see solidDB Speed Loader control file syntax.

The CHARACTERSET keyword is used to define the character set used in the input file. If the CHARACTERSET keyword is not used or if it is used with the parameter NOCONVERT or NOCNV, no conversions are made.

The CHARACTERSET keyword must be used with one of the following strings:

The DATE, TIME, and TIMESTAMP keywords can be used in two places with different functionality:

Data must be of the same type in the import-file, the mask, and the column in the table into which the data is loaded.

The following example uses the POSITION keyword.

The following example uses the FIELDS TERMINATED BY keyword.

The PRESERVE BLANKS keyword is used to preserve all blanks in text fields.

The into_table_part element is used to define the name of the table and columns that the data is inserted into.

The FIELDS ENCLOSED BY clause defines delimiting characters around each field. The delimiter can be a single character or two separate characters that precede and follow each data field in the input file. You might use a single character (such as the double quotation mark character) or a pair of characters (such as left and right parentheses) to delimit your fields. If you use double quotation marks as the delimiter and the comma as the terminator/separator, your input might look like the following:

If you use left and right parentheses, your input might look like the following:

If the keyword OPTIONALLY is used, the delimiters are optional and do not need to appear around every single piece of data.

If you specify a character value, it must be enclosed in single or double quotation marks. For example, the following examples have the same effect:

Almost any printable characters can be used as the "enclosing" characters. The enclosing characters can also be specified using the hexadecimal format. For example, if a hexadecimal string is used, the format is:

For example, X'3a' means 3A hexadecimal value and specifies the colon (:).

The opening and closing characters in an enclosing pair can be identical. For example, the following is valid inside the control file:

If both the opening and closing characters are the same, the ENCLOSED BY clause can specify the character just once. For example, the following clauses have the same effect:

You can use enclosing characters in the column data itself (embedded field separators). If you use embedded field separators, you can use the TERMINATED BY clause together with the OPTIONALLY ENCLOSED BY clause to ensure that the column data is enclosed correctly.

This section contains basic rules and examples when using enclosing characters. Each example, unless stated otherwise, contains the following control file lines:

The enclosing characters are parentheses and the separator (terminator) character is the colon (:); hexadecimal 3A specifies the colon.

The data is to be loaded into a table with two columns. The first column is of type VARCHAR and the second of type INTEGER.

The ENCLOSED BY characters themselves can occur within the data. However, when occurring within the data, each of the enclosing characters must occur twice in the input for each occurrence in the database.

If parentheses are used as the enclosing characters and the input file contains the following string:

the following value is entered in the database:

Deeply nested parentheses work the same way. If the input file contains the following string:

the following value is entered into the first column of the table:

The final enclosing character must occur an odd number of times at the end of the input. For example to get the text High Availability Controller (HAC) in the database, the input file must contain the following string

Of the last three closing parentheses, the first two are treated as a single instance of the character, while the last one is treated as the enclosing character.

When enclosing characters are used, newline characters (carriage return or line feed) can be embedded within a string. For example:

If the field separator (colon) is not used in the data and if there is no need to preserve new lines in the input data, only the field separator (not the enclosing characters) is required in the input data.

If your data is fixed-width, you do not need either the separator or the enclosing characters.

The FIELDS TERMINATED BY clause is used to define the separator character that distinguishes where fields end in the input file. The character must be specified in one of the following three ways:

When using hexadecimal format, the quotation marks must be single quotation marks, not double quotation marks.

The FIELDS TERMINATED BY clause specifies a separator, not a true terminator. The specified character is not required after the last field. For example, if the colon is the separator, the following two data file formats are equivalent and valid:

or

The trailing colon after the final field is accepted but not required.

The OPTIONALLY ENCLOSED BY clause is used after the FIELDS TERMINATED BY clause when the character used to enclose the column data is contained in the column data itself. Following is a control file example:

In the this example, the separator is a comma.

The single quotation mark is defined as the character that encloses embedded field separators (commas) in the data file. The OPTIONALLY ENCLOSED BY clause can use either single or double quotation marks to delimit the enclosing characters.

For example:

The following example summarizes the use of separators and enclosing characters. In this example, the : (colon) is defined as the separator (FIELDS TERMINATED BY) and the parentheses are used to enclose the : (colon), which is embedded in the field and cannot be interpreted as a separator. The example also contains two fields, the first of which is VARCHAR and the second of which is INTEGER.

The POSITION keyword is used to define position of a field in the logical record. Both the start and the end position must be defined.

The NULLIF keyword is used to give a column a NULL value if the appropriate field has a specified value. An additional keyword specifies the value the field must have. The keyword BLANKS sets a NULL value if the field is empty; the keyword NULL sets a NULL value if the field is the string "NULL"; the definition string sets a NULL value if the field matches the string 'string'; the definition '((start : end) = 'string')' sets a NULL value if a specified part of the field matches the string 'string'.

The following example shows the use of the NULLIF keyword with the keyword BLANKS to set a NULL value if the field is empty. It also shows the use of the keyword NULL to set a NULL value if the field is the string "NULL".

The following example uses the definition '((start:end) = 'string')' for the third field in the input file. This syntax only works with fixed-width fields because the exact position of the 'string' must be specified.