In line 2 the value for A contains a space " 1".

That might cause the issue.

You can change the reader to read the csv as char fields.

Then use the AttributeTrimmer to get rid of the unwanted spaces and write to a Numeric output.

Thank you, it's line 2 (after the header) that's the problem.

QA456,,,,

Same solution.

Change the input data type to char as in numeric fields the comma can be a separator.

But as far as I know the line 2 in the error message is the second line in the CSV (including the line to skip).

For more information to remove confusion over which line.

this is the Table creation:

CREATE TABLE andy.test
(id character varying NOT NULL,a numeric,b numeric,c numeric,d numeric);

This is the file:

ID,A,B,C,D
QA123,1,2,3,4
QA456,,,,

The copied error from the log:

Bulk copy failed on table 'andy.test' using delimiter ':'. Error was 'ERROR:  invalid input syntax for type numeric: ""
CONTEXT:  COPY test, line 2, column a: ""

The first row (QA123) is inserted correctly if I do a commit after every record.

Thanks

Andy

The problem is that the CSV reader maps the empty values to empty strings, and not to NULL.

You can easily solve this using an NullAttributeMapper, like this:

Your empty string values will now be mapped to NULL, which should render them acceptable to the database.

David

Aha, the issue is not the CSV reader but the writer trying insert non_numeric data into a numeric table field.

Have you tried the AttributeTrimmer before writing (with the setting to allow attributes to be trimmed to nothing set to Yes)?

BINGO! Thanks