Reading a csv file with null entries
ID, A, B, C, D
QA123, 1,2,3,4
QA456,,,,
Into a table with character_varying, numeric, numeric, numeric, numeric
Writer fails on line QA456 with error
Error was 'ERROR: invalid input syntax for type numeric: ""
CONTEXT: COPY table, line 2, column A: ""
How can I deal with this issue?
Thanks
Using FME Desktop 2015.1
Best answer by david_r
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
+23In 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.
+23Same 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
+23Aha, 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)?
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
BINGO! Thanks
