6. QUEL and EQUEL Statements : Copy Statement—Copy Data : Column Formats for COPY : Delimiters in the Data File
 
Delimiters in the Data File
Delimiters are those characters in the data file that separate fields and mark the end of records. Valid delimiters are listed in the following table:
Delimiter
Description
nl
newline character
tab
tab character
sp
space
csv
comma separated values
ssv
semicolon separated values
nul or null
null character
comma
comma
colon
colon
dash
dash
lparen
left parenthesis
rparen
right parenthesis
x
any non-numeric character
When a single character is specified as the delimiter, enclose that character in quotes. If the data type specification is C or D, the quotes must enclose the entire format. For example, 'd0%' specifies a dummy column delimited by a percent sign (%). If the data type specification uses parentheses around the length, quote only the delimiter. For example, char(0)'%' specifies a char field delimited by a percent sign.
Be careful using the sp (space) or null delimiters, especially with COPY FROM. Spaces or nulls are used as padding characters by many of the COPY formats. If a pad character is improperly treated as a delimiter, the COPY FROM will get out of sync with the input, eventually producing an error. When designing a data file format, use delimiters that will not appear in the data or padding, or use CSV or SSV forms.
When copying from a table into a file, you can insert delimiters independently of columns. For example, to insert a newline character at the end of a line, specify nl=d1 at the end of the column list. This directs the DBMS Server to add one (d1) newline (nl) character. (Do not confuse lowercase "l" with the number "1".)