2014-01-13

Detect Invalid DateTime String with DateFormatter

(FME 2014 Beta build 14229)

"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.
DateRainfall
2013-04-010.0
2013-04-0253.5
2013-04-0360.0
2013-04-04
2013-04-050.0
2013-04-0699.5
2013-04-079.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.
=====

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