Notes on the delimited import file (CPYTOIMPF command)

Edit online

This topic contains information about using the CPYTOIMPF command with delimited import files.

Blanks
You can use the RMVBLANK parameter to handle blank characters from the character fields. The RMVBLANK parameter has the following options:
  • *NONE: All leading and trailing blanks are retained.
  • *LEADING: Leading blanks are removed. This is the default value.
  • *TRAILING: Trailing blanks are removed.
  • *BOTH: Leading and trailing blanks are removed.
Note: Removal of blanks, as specified by the RMVBLANK parameter, takes precedence over string delimiters.
Null fields
If a field is null, the field contains two adjacent field delimiters (no data in between).
Delimiters
  • A delimiter cannot be a blank.
  • A string delimiter or a string escape character cannot be the same as a field delimiter, a record delimiter, a decimal point, a date separator, or a time separator.
  • A field delimiter cannot be the same as a record delimiter.
  • When a string delimiter character is contained by a string, precede the string delimiter in the string with the string escape character specified with the STRESCCHR parameter.
  • The defaults for delimiters are as follows:
    • String is: *DBLQUOTE (double quotation mark)
    • Field is: , (comma)
    • Decimal point is: . (period)
    • Record is: *EOR (end of record)
String escape character
When a string delimiter character is contained by a string, you can use the STRESCCHR parameter to specify a string escape character for indicating that the string delimiter character should be treated as a normal character instead of string delimiter.
The STRESCCHR parameter has the following options:
  • *NONE: No string escape character is used.
  • *STRDLM: The string delimiter is used as the string escape character.
  • character-value: The character-value is used as the string escape character.
Numeric fields
Decimal points are either a period or a comma (command option).
Graphic fields
The string delimiter is placed around all graphic data. If graphic data is contained in the file and the string delimiter is the *NONE value, an error is signaled.
All fields
The CAST function in SQL copies the data from the from-file to the to-file. All data is copied and the relative record numbers of the from-file and to-file are the same, unless the from-file contains deleted records. Deleted records are not copied.
CCSIDs
The data from the from-file is read into the to-file's CCSID.

If the CCSID of the from-file field is 65535, the data will be converted from the CCSID specified in the FROMCCSID parameter.

The Stream file CCSID (STMFCCSID) parameter is used to specify the method of obtaining the stream file CCSID according to the code page that is used for data conversion. This parameter can be used to replace the Stream file code page (STMFCODPAG) parameter.

Date fields
All date formats supported by the IBM i operating system can be exported (*ISO, *USA, *EUR, *JIS, *MDY, *DMY, *YMD, *JUL, *YYMD).
Time fields
All time formats supported by the IBM i operating system can be exported (*ISO, *USA, *EUR, *JIS, *HMS).
Date and time separators
All valid separators are supported for date and time fields.
Timestamp fields
Timestamp export fields must be 26 bytes.