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.