Data Movement Utilities Guide and Reference

Delimited ASCII (DEL) File Format

A Delimited ASCII (DEL) file is a sequential ASCII file with row and column delimiters. Each DEL file is a stream of ASCII characters consisting of cell values ordered by row, and then by column. Rows in the data stream are separated by row delimiters; within each row, individual cell values are separated by column delimiters.

The following table describes the format of DEL files that can be imported, or that can be generated as the result of an export action.

DEL file ::= Row 1 data || Row delimiter || Row 2 data || Row delimiter || . . . Row n data || Optional row delimiter Row i data ::= Cell value(i,1) || Column delimiter || Cell value(i,2) || Column delimiter || . . . Cell value(i,m) Row delimiter ::= ASCII line feed sequence^a Column delimiter ::= Default value ASCII comma (,)^b

Cell value(i,j) ::= Leading spaces || ASCII representation of a numeric value (integer, decimal, or float) || Delimited character string || Non-delimited character string || Trailing spaces Non-delimited character string ::= A set of any characters except a row delimiter or a column delimiter Delimited character string ::= A character string delimiter || An extended character string || A character string delimiter || Trailing garbage Trailing garbage ::= A set of any characters except a row delimiter or a column delimiter Character string delimiter ::= Default value ASCII double quotation marks (")^c extended character string ::= || A set of any characters except a row delimiter or a character string delimiter if the NODOUBLEDEL modifier is specified || A set of any characters except a row delimiter or a character string delimiter if the character string is not part of two consecutive character string delimiters || A set of any characters except a character string delimiter if the character string delimiter is not part of two consecutive character string delimiters, and the DELPRIORITYCHAR modifier is specified End-of-file character ::= Hex '1A' (OS/2 or the Windows operating system only) ASCII representation of a numeric value^d ::= Optional sign '+' or '-' || 1 to 31 decimal digits with an optional decimal point before, after, or between two digits || Optional exponent Exponent ::= Character 'E' or 'e' || Optional sign '+' or '-' || 1 to 3 decimal digits with no decimal point Decimal digit ::= Any one of the characters '0', '1', ... '9' Decimal point ::= Default value ASCII period (.)^e

^a The record delimiter is assumed to be a new line character, ASCII x0A. Data generated on OS/2 or the Windows operating system can use the carriage return/line feed 2-byte standard of 0x0D0A. Data in EBCDIC code pages should use the EBCDIC LF character (0x25) as the record delimiter (EBCDIC data can be loaded using the CODEPAGE option on the LOAD command).
^b The column delimiter can be specified with the COLDEL option.
^c The character string delimiter can be specified with the CHARDEL option.
Note:
The default priority of delimiters is:

Record delimiter
Character delimiter
Column delimiter
See also the description of the delprioritychar modifier in Table 8.
^d If the ASCII representation of a numeric value contains an exponent, it is a FLOAT constant. If it has a decimal point but no exponent, it is a DECIMAL constant. If it has no decimal point and no exponent, it is an INTEGER constant.
^e The decimal point character can be specified with the DECPT option.

Sample DEL File

Following is an example of a DEL file. Each line ends with a line feed sequence (on OS/2 or the Windows operating system, each line ends with a carriage return/line feed sequence).

   "Smith, Bob",4973,15.46
   "Jones, Bill",12345,16.34
   "Williams, Sam",452,193.78

The following example illustrates the use of non-delimited character strings. The column delimiter has been changed to a semicolon, because the character data contains a comma.

   Smith, Bob;4973;15.46
   Jones, Bill;12345;16.34
   Williams, Sam;452;193.78

Notes:

A space (X'20') is never a valid delimiter.
Spaces that precede the first character, or that follow the last character of a cell value, are discarded during import. Spaces that are embedded in a cell value are not discarded.
A period (.) is not a valid character string delimiter, because it conflicts with periods in time stamp values.
For pure DBCS (graphic), mixed DBCS, and EUC, delimiters are restricted to the range of x00 to x3F, inclusive.
For DEL data specified in an EBCDIC code page, the delimiters may not coincide with the shift-in and shift-out DBCS characters.
On OS/2 or the Windows operating system, the first occurrence of an end-of-file character (X'1A') that is not within character delimiters indicates the end-of-file. Any subsequent data is not imported.
A null value is indicated by the absence of a cell value where one would normally occur, or by a string of spaces.
Since some products restrict character fields to 254 or 255 bytes, the export utility generates a warning message whenever a character column of maximum length greater than 254 bytes is selected for export. The import utility accommodates fields that are as long as the longest LONG VARCHAR and LONG VARGRAPHIC columns.

DEL Data Type Descriptions

Table 9. Acceptable Data Type Forms for the DEL File Format

Data Type Form in Files Created by the Export Utility Form Acceptable to the Import Utility
BIGINT An INTEGER constant in the range -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807. ASCII representation of a numeric value in the range -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807. Decimal and float numbers are truncated to integer values.
BLOB, CLOB Character data enclosed by character delimiters (for example, double quotation marks). A delimited or non-delimited character string. The character string is used as the database column value.
BLOB_FILE, CLOB_FILE The character data for each BLOB/CLOB column is stored in individual files, and the file name is enclosed by character delimiters. The delimited or non-delimited name of the file that holds the data.
CHAR Character data enclosed by character delimiters (for example, double quotation marks). A delimited or non-delimited character string. The character string is truncated or padded with spaces (X'20'), if necessary, to match the width of the database column.
DATE yyyymmdd (year month day) with no character delimiters. For example: 19931029
Alternatively, the DATESISO option can be used to specify that all date values are to be exported in ISO format.
A delimited or non-delimited character string containing a date value in an ISO format consistent with the country code of the target database, or a non-delimited character string of the form yyyymmdd.
DBCLOB (DBCS only) Graphic data is exported as a delimited character string. A delimited or non-delimited character string, an even number of bytes in length. The character string is used as the database column value.
DBCLOB_FILE (DBCS only) The character data for each DBCLOB column is stored in individual files, and the file name is enclosed by character delimiters. The delimited or non-delimited name of the file that holds the data.
DECIMAL A DECIMAL constant with the precision and scale of the field being exported. The DECPLUSBLANK option can be used to specify that positive decimal values are to be prefixed with a blank space instead of a plus sign (+). ASCII representation of a numeric value that does not overflow the range of the database column into which the field is being imported. If the input value has more digits after the decimal point than can be accommodated by the database column, the excess digits are truncated.
FLOAT(long) A FLOAT constant in the range -10E307 to 10E307. ASCII representation of a numeric value in the range -10E307 to 10E307.
GRAPHIC (DBCS only) Graphic data is exported as a delimited character string. A delimited or non-delimited character string, an even number of bytes in length. The character string is truncated or padded with double-byte spaces (for example, X'8140'), if necessary, to match the width of the database column.
INTEGER An INTEGER constant in the range -2 147 483 648 to 2 147 483 647. ASCII representation of a numeric value in the range -2 147 483 648 to 2 147 483 647. Decimal and float numbers are truncated to integer values.
LONG VARCHAR Character data enclosed by character delimiters (for example, double quotation marks). A delimited or non-delimited character string. The character string is used as the database column value.
LONG VARGRAPHIC (DBCS only) Graphic data is exported as a delimited character string. A delimited or non-delimited character string, an even number of bytes in length. The character string is used as the database column value.
SMALLINT An INTEGER constant in the range -32 768 to 32 767. ASCII representation of a numeric value in the range -32 768 to 32 767. Decimal and float numbers are truncated to integer values.
TIME hh.mm.ss (hour minutes seconds). A time value in ISO format enclosed by character delimiters. For example: "09.39.43" A delimited or non-delimited character string containing a time value in a format consistent with the country code of the target database.
TIMESTAMP yyyy-mm-dd-hh.mm.ss.nnnnnn (year month day hour minutes seconds microseconds). A character string representing a date and time enclosed by character delimiters. A delimited or non-delimited character string containing a time stamp value acceptable for storage in a database.
VARCHAR Character data enclosed by character delimiters (for example, double quotation marks). A delimited or non-delimited character string. The character string is truncated, if necessary, to match the maximum width of the database column.
VARGRAPHIC (DBCS only) Graphic data is exported as a delimited character string. A delimited or non-delimited character string, an even number of bytes in length. The character string is truncated, if necessary, to match the maximum width of the database column.

Data Type	Form in Files Created by the Export Utility	Form Acceptable to the Import Utility
BIGINT	An INTEGER constant in the range -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807.	ASCII representation of a numeric value in the range -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807. Decimal and float numbers are truncated to integer values.
BLOB, CLOB	Character data enclosed by character delimiters (for example, double quotation marks).	A delimited or non-delimited character string. The character string is used as the database column value.
BLOB_FILE, CLOB_FILE	The character data for each BLOB/CLOB column is stored in individual files, and the file name is enclosed by character delimiters.	The delimited or non-delimited name of the file that holds the data.
CHAR	Character data enclosed by character delimiters (for example, double quotation marks).	A delimited or non-delimited character string. The character string is truncated or padded with spaces (X'20'), if necessary, to match the width of the database column.
DATE	yyyymmdd (year month day) with no character delimiters. For example: 19931029 Alternatively, the DATESISO option can be used to specify that all date values are to be exported in ISO format.	A delimited or non-delimited character string containing a date value in an ISO format consistent with the country code of the target database, or a non-delimited character string of the form yyyymmdd.
DBCLOB (DBCS only)	Graphic data is exported as a delimited character string.	A delimited or non-delimited character string, an even number of bytes in length. The character string is used as the database column value.
DBCLOB_FILE (DBCS only)	The character data for each DBCLOB column is stored in individual files, and the file name is enclosed by character delimiters.	The delimited or non-delimited name of the file that holds the data.
DECIMAL	A DECIMAL constant with the precision and scale of the field being exported. The DECPLUSBLANK option can be used to specify that positive decimal values are to be prefixed with a blank space instead of a plus sign (+).	ASCII representation of a numeric value that does not overflow the range of the database column into which the field is being imported. If the input value has more digits after the decimal point than can be accommodated by the database column, the excess digits are truncated.
FLOAT(long)	A FLOAT constant in the range -10E307 to 10E307.	ASCII representation of a numeric value in the range -10E307 to 10E307.
GRAPHIC (DBCS only)	Graphic data is exported as a delimited character string.	A delimited or non-delimited character string, an even number of bytes in length. The character string is truncated or padded with double-byte spaces (for example, X'8140'), if necessary, to match the width of the database column.
INTEGER	An INTEGER constant in the range -2 147 483 648 to 2 147 483 647.	ASCII representation of a numeric value in the range -2 147 483 648 to 2 147 483 647. Decimal and float numbers are truncated to integer values.
LONG VARCHAR	Character data enclosed by character delimiters (for example, double quotation marks).	A delimited or non-delimited character string. The character string is used as the database column value.
LONG VARGRAPHIC (DBCS only)	Graphic data is exported as a delimited character string.	A delimited or non-delimited character string, an even number of bytes in length. The character string is used as the database column value.
SMALLINT	An INTEGER constant in the range -32 768 to 32 767.	ASCII representation of a numeric value in the range -32 768 to 32 767. Decimal and float numbers are truncated to integer values.
TIME	hh.mm.ss (hour minutes seconds). A time value in ISO format enclosed by character delimiters. For example: "09.39.43"	A delimited or non-delimited character string containing a time value in a format consistent with the country code of the target database.
TIMESTAMP	yyyy-mm-dd-hh.mm.ss.nnnnnn (year month day hour minutes seconds microseconds). A character string representing a date and time enclosed by character delimiters.	A delimited or non-delimited character string containing a time stamp value acceptable for storage in a database.
VARCHAR	Character data enclosed by character delimiters (for example, double quotation marks).	A delimited or non-delimited character string. The character string is truncated, if necessary, to match the maximum width of the database column.
VARGRAPHIC (DBCS only)	Graphic data is exported as a delimited character string.	A delimited or non-delimited character string, an even number of bytes in length. The character string is truncated, if necessary, to match the maximum width of the database column.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]