No nl Delimiter in the Copy File
When using fixed-length specifications in the COPY statement, you must account for the “nl” (newline) character at the end of the record.
For example, you receive an error message if you try to copy these records:
554-39-2699 01-oct-1998 Quinn 28000.00 Programmer
335-12-1452 23-jun-1998 Smith 79000.00 Sr Analyst
with the following COPY statement:
COPY TABLE personnel (ssno = CHAR(12),
birthdate = CHAR(12),
name = CHAR(6),
salary = CHAR(9),
title = CHAR(10))
FROM 'pers.data';
The format specified for the title field is char(10), which does not account for the newline character. The newline characters are converted to blanks, and the extra characters force the COPY statement to begin reading a third record that ends abnormally with an unexpected end of file. Use char(10)nl to avoid this problem.
If you are loading a standard Comma-Separated Values (CSV) format data file, you can use the CSV (or SSV) delimiter for all of the COPY list items. The CSV delimiter automatically looks for a newline at the end of each file record. For details on delimiters, see the SQL Reference Guide.