SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Utilities
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
5-57
Considerations for import
If the data is enclosed within field qualifiers, HP recommends that you use a unique
multicharacter string as a field delimiter that can be distinguished from the enclosed
data.
Using a Field Delimiter
Use field delimiters to separate field data for a record. The default field delimiter
character is a comma (','). HP recommends that you use a unique multicharacter field
delimiter string that is not part of enclosed or nonenclosed field data. For example:
135|*| "Re: "Meeting Request|*|" subject" |*| 01-JUL01985
In this example, the field delimiter is part of the data. When import processes this
type of data, as soon as it encounters the first |*| in the data that corresponds to the
second field, it is treated as a field delimiter. import then processes the data following
this first |*| as next field data, and so on through the data. Therefore, in this example,
the column count of the data is considered to be more than the table column count.
Row or Record Delimiters
The new line character (\n) is typically used as a record delimiter in an input data file. If
a new line character already exists in an input file as a record delimiter, you cannot
specify and include a different record delimiter in the file. If you do, import interprets
the new line character as part of a data field.
Under some circumstances, you might want to include a new line character as part of a
data field. For example, suppose that you have data that is to be used as printed text,
and the new line character is included in the data for the purpose of formatting. Then
you must specify a record delimiter other than the new line character.
Null Input Values for Delimited Data Input Files
For a delimited input file, if a column in the target table allows null, you can specify null
for that column in the input file. Two consecutive field delimiters specify null.
For example, suppose that the EMPLOYEE target table begins with the columns
EMPNUM, FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME. The MIDDLE_INITIAL
column allows null. Some employees have no middle initial. As a result, the input file
contains records like this:
2961,Mary,,Smith,143,3490,80000.00
To insert null in a nullable target column, you can specify two consecutive field
delimiters as shown in the preceding example. To insert blanks in the target column,
you can specify two field delimiters with the appropriate number of blanks between the
delimiters.
Default Values for Delimited Data Input Files
For a delimited input file, if a column in the target table does not allow null, using two
consecutive field delimiters directs import to use the default value for the column
(instead of null for the column). For example, a column defined as NOT NULL might
have space as its default value. In this case, two consecutive field delimiters in the
corresponding input field specify that sp
ace is to be stored in the target column.