"Set Invalid Date Attributes To" parameter has been added to the DateFormatter transformer in FME 2014.
In the previous version, the DateFormetter just does nothing if a datetime string is stored in invalid format. So it was necessary to use a regular expression or similar mechanism to determine whether the format is valid or not. Now it can be determined with simpler way.
This is part of a CSV table describing daily rainfall records in Tokyo, 2013.
Note: <empty> Rainfall indicates no rain, 0.0 indicates rained but the value was less than 0.05 mm/day. Those two representations have to be strictly distinguished.
Date | Rainfall |
---|---|
2013-04-01 | 0.0 |
2013-04-02 | 53.5 |
2013-04-03 | 60.0 |
2013-04-04 | |
2013-04-05 | 0.0 |
2013-04-06 | 99.5 |
2013-04-07 | 9.5 |
2013-04-08 | |
2013-04-09 | |
2013-04-10 |
The correct date format is "%Y-%m-%d". Assume that I need to validate and change date format for the following processing - e.g. writing the records into a database table.
I think a combination of a DateFormatter and a Tester can be used effectively in this case. That is to say, a Tester can be used to detect invalid records easily if the "Set Invalid Date Attributes To" parameter is specified like this.
=====
2014-01-19: Of course an invalid date string can be replaced with <null> in the DateFormatter, and also the Tester can be used to determine whether an attribute value is <null>. Using <null> might be more intuitive than replacing with specific string value.
=====
=====
2014-01-19: Of course an invalid date string can be replaced with <null> in the DateFormatter, and also the Tester can be used to determine whether an attribute value is <null>. Using <null> might be more intuitive than replacing with specific string value.
=====
Adding a NullAttributeMapper
When the MDB Writer receives an <empty> Rainfall, it would write <null> into the associated field in the destination table, because non-numeric attribute value (including empty string) cannot be converted to a decimal type value. Then the writer logs a warning message like this.
MS Access Writer: 179 attribute value(s) failed conversion, and were written as NULL values
Although the destination table would be written as expected, I would insert a NullAttributeMapper between the Tester and the Writer in order to convert <empty> to <null> explicitly, so that other more important warnings which have to be detected will not be overlooked.
I think it would be one of general NullAttributeMapper use cases.
No comments:
Post a Comment