6. QUEL and EQUEL Statements : Copy Statement—Copy Data : Column Formats for COPY : COPY Format Details
 
Share this page                  
COPY Format Details
This section describes specifying the format of fields in the data file. When specifying data file formats for COPY INTO, be aware of the following points:
• Data from numeric columns, when written to text fields in the data file, is right-justified and filled with blanks on the left.
• When a COPY INTO statement is issued in the Terminal Monitor, the –i and –f command line flags control the format used to convert floating-point table data into text-type file data. To avoid rounding of large floating point values, use the sql command -f flag to specify a floating point format that correctly accommodates the largest value to be copied. For information about the -i and -f flags, see the quel command description in the Command Reference Guide.
• The COPY INTO section often uses the phrase "the display length of the corresponding table column". This means the length of the table column when formatted as a character string. This will be a standard length based on the table column type, and is independent of the actual column value. For example: the display length of an INTEGER column is 13, the display length of a SMALLINT column is 6, and so on.
The following table explains the details for the various COPY list formats. Unless otherwise noted, all non-binary formats can be followed by an optional delim to specify a delimiter.
Format
How Stored (COPY INTO)
How Read (COPY FROM)
BYTE(0)
Same as BYTE(n) where n is the display length of the corresponding table column.
Read as variable-length binary data terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value.
BYTE(n) where n
is 1 to the maximum row size configured, not exceeding 32,000.
Written as a fixed-length byte string. Exactly n bytes are written, padded with zeros if necessary. If given, the delimiter is written after the value and padding.
Read as a fixed-length byte string; exactly n bytes are read. If a delimiter is specified, one additional character is read and discarded.
BYTE VARYING(0)
Same as BYTE VARYING(n) where n is the display length of the table column.
Read as a variable-length byte string, preceded by a 5‑character, right‑justified length specifier. If a delimiter is specified, additional input is discarded until the delimiter is found.
BYTE VARYING(n) where n is 1 to the maximum row size configured, not exceeding 32,000.
Written as a fixed-length byte string preceded by a 5-character, right-justified length specifier. If necessary, the field is padded with zeros to the specified length. If given, the delimiter is written after the value and padding.
Read as a fixed-length byte string, preceded by a 5-character, right-justified length specifier. If a delimiter is specified, one additional character is read and discarded.
C0
Same as Cn where n is the display length of the corresponding table column
Read as a variable-length string, terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value.
Any control characters or tabs in the input are converted to spaces. C0 format supports \. The \ is discarded, and the next character is taken literally as part of the value (even if it would normally be the delimiter). To read a \ character, use \\.
Cn
Written as a fixed-length string, padded with blanks if necessary. Any "non-printing" character (meaning a control character or tab) is converted to a space. If given, the delimiter is written after the value and padding.
Read as a fixed-length string. If a delimiter is specified, one additional character is read and discarded.
Any control characters or tabs in the input are converted to spaces. Fixed-length Cn format does not support \.
CHAR(0)
Same as CHAR(n) where n is the display length of the corresponding table column.
Read as a variable-length string terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value.
Unlike C format, CHAR does not support \. CHAR also does not convert control characters or tabs. File data is read as is.
CHAR(n)
where n is 1 to the maximum row size configured, not exceeding 32,000 (16,000 in a UTF8 instance).
Written as a fixed-length string, padded with blanks if necessary. If given, the delimiter is written after the value and padding.
Unlike C format, CHAR does not do any conversion of control characters or tabs. Table data is output as-is.
Read as a fixed-length string. If a delimiter is specified, one additional character is read and discarded.
Unlike C format, CHAR does not convert control characters or tabs. File data is read as is.
D0
Instead of placing a value in the file, COPY writes the specified delimiter. (Unlike the Dn format, D0 format does not write the column name.)
COPY INTO requires that a delimiter be specified; D0 with no delimiter is not allowed.
Dummy field. Characters are read and discarded until the specified delimiter is encountered. If a delimiter is not specified, the first comma, tab, or newline ends the value.
Any \ found in the input means that the next character is to be taken literally, and is not a delimiter.
Dn
Dummy column. Instead of placing a value in the file, COPY writes the name of the column n times. For example, if you specify x=D1, the column name, x, is written once; if you specify x=D3, COPY writes xxx (the column name, three times), and so on. You can specify a delimiter as a column name, for example, NL=D1.
Dummy field. N characters are read and discarded.
COPY FROM does not allow a delimiter specification with a fixed-length dummy field.
DATE
Written as a date. (A binary format)
Read as a date. (A binary format)
DECIMAL
Written as a decimal number. (A binary format.)
Read as a decimal number. (A binary format)
FLOAT
Written as double‑precision floating point. (A binary format)
Read as double‑precision floating point. (A binary format)
FLOAT4
Written as single‑precision floating point. (A binary format)
Read as single‑precision floating point. (A binary format)
INTEGER
Written as integer of 4‑byte length. (A binary format)
Read as integer of 4‑byte length. (A binary format)
INTEGER1
Written as integer of 1‑byte length. (A binary format)
Read as integer of 1‑byte length. (A binary format)
MONEY
Written as a scaled floating point value (a money value). (A binary format)
Read as a scaled floating-point values (a money value). (A binary format)
SMALLINT
Written as an integer of 2‑byte length. (A binary format.)
Read as integer of 2‑byte length. (A binary format)
TEXT(0)
Written as a variable length string. If a delimiter is specified, it is written after the value.
If the originating column is C, CHAR, or NCHAR, trailing blanks are trimmed. If the originating column is TEXT, VARCHAR, or NVARCHAR, the column value is output exactly as-is (no padding, no trimming). If the originating column is a non-character, writes the result of converting the value to a character string, as-is with no padding.
COPY INTO using TEXT(0) format is the way to get variable width output with no padding.
Read as variable-length character string terminated by the specified delimiter. If a delimiter is not specified, the first comma, tab, or newline encountered ends the value
TEXT(n)
Written as a fixed-length string. The value is padded with NULL bytes (zeros) if necessary. If specified, the delimiter is written after the value and padding.
Reads a fixed-length field n characters wide; however if one of those characters is a NULL byte, the value stored into the table is terminated at that NULL byte. If a delimiter is specified, one additional character is read and discarded.
VARCHAR(0)
Same as VARCHAR(n), where n is the display length of the corresponding table column.
Read as a variable-length string, preceded by a 5‑character, right-justified length specifier. If a delimiter is specified, additional input is discarded until the delimiter is found.
VARCHAR(n)
where n is 1 to the maximum row size configured, not exceeding 32,000 (16,000 in a UTF8 instance).
Written as a fixed-length string preceded by a 5-character, right‑justified length specifier. If necessary, the value is padded with null characters to the specified length.
Read as a fixed-length string, preceded by a 5-character, right-justified length specifier. If a delimiter is specified, one additional character is read and discarded.
Note:  The dummy format (dn) behaves differently for COPY FROM and COPY INTO. When a table is copied into a file, n specifies the number of times the column name is repeated. When copying from a file to a table, n specifies the number of bytes to skip.
For user-defined data types (UDTs), use CHAR or VARCHAR.