Reloading Problems
When using the COPY FROM statement, the following problems in the copy file are the most frequent causes for error messages:
• Invalid data
• Miscounting fixed-length field widths
• Neglecting the nl delimiter in the COPY statement
• Omitting delimiters between fields
• Including too many delimiters
Invalid Data in the Copy File
If you try to load invalid data into a field, the row is rejected.
For example, the following record is rejected because February has only twenty-eight or twenty-nine days:
559-58-2543,31-feb-1998,Weir,100000.00,Executive
Miscounted Fixed-Length Field Widths in the Copy File
If the widths of fixed-length fields are not correct, the COPY statement can try to include data in a field that it cannot convert to the appropriate format.
For example, you receive an error message if you try to copy this row:
554-39-2699 01-oct-1998 Quinn 28000.00 Assistant
with the following COPY statement:
COPY TABLE personnel (ssno = CHAR(20),
birthdate = CHAR(11),
name = CHAR(11),
salary = CHAR(9),
title = CHAR(0)nl)
FROM 'pers.data';
Because you specified char(20), or 20-character positions, for the ssno field, the COPY statement includes part of the birth date in the value for the ssno field. When the COPY statement tries to read the birth date, it reads “998 Quinn 2” which is not a valid date if birth date is defined as a date field; if defined as a char field, you get an “unexpected EOF” error.
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.
Omitted Delimiters Between Fields in the Copy File
If you omit delimiters between fields in the data file, the record is rejected.
For example, the first record below has no delimiter between the employee’s name and her salary:
123-45-6789,01-jan-1998,Garcia33000.00,Programmer246-80-1357,02-jan-1998,Smith,43000.00,Coder
If you try to copy these records with the following COPY statement, you receive an error message because the COPY statement attempts to read “Programmer” into the “salary” field:
COPY TABLE personnel
(ssno = CHAR(0),
birthdate = CHAR(0),
name = CHAR(0),
salary = CHAR(0),
title = CHAR(0)NL)
FROM 'pers.data';
Too Many Delimiters in the Copy File
Be careful not to include too many delimiters in the data file. This mistake frequently occurs when you use the comma as a delimiter and it also appears in the data.
For example, in the first row, the salary value contains a comma:
123-45-6789,01-jan-1998,Garcia,33,000.00,Programmer
246-80-1357,02-jan-1998,Smith,43000.00,Coder
If you try to copy these records with the following COPY statement, you receive an error message:
COPY TABLE personnel
(ssno = CHAR(0),
birthdate = CHAR(0),
name = CHAR(0),
salary = CHAR(0),
title = CHAR(0))
FROM 'pers.data';
You receive an error because the COPY statement reads:
• “33” as the “salary”
• “000.00” as the “title”
• “Programmer” as the next “ssno”
It attempts to read “246-80-1357” as the birthdate, which produces the error.
If you specified “title = char(0)nl”, the COPY statement still reads “33” as the salary, but it reads “000.00,Programmer” as the title. This is because it looks for a newline rather than a delimiter at the end of the title. It reads the next row correctly. Although an error message is not generated, the title field for one row is incorrect.