1

Using FME(R) 2023.1.1.1 (20231011 - Build 23636 - WIN64) at the time of writing, I'd like to format a date field from an Excel spreadsheet into an ISO date which should then be written to a PostgreSQL (13) database (as a PG date field).

To this end, the workspace I'm using has an old DateFormatter transformer. It is as follow:

enter image description here

And it used to help me a lot in this task because there are some few comments (strings) in that date column in the Excel data set. Upon those comments, some cells are simple having one or more white space characters. I need to set those dates which are not recognized as a valid dates by FME, to null prior to the writing in the PostgreSQL database.

Happily, the DateFormatter offers the option to set invalid date attributes to either null (which is what I need) or to keep the original value:

enter image description here

At this stage, the first trouble I face is that white space characters are actually NOT set to null but kept "as is", and FME reports that those features as successfully written to the database, without any kind of errors or warnings, but they are actually NOT inserted and this a major flaws.

When searching to debug this issue, I noticed that the DateFormatter has been kind of deprecated because each time I want to add such transformer in the workspace, it defaults to a DateTimeConvert instead (also, there is not recent documentation on the former transformer).

In this new, DateTimeConvert, there is no such "replace by null" (or keep the original value) in case the date is not a valid date format:

enter image description here


So my question is:

How could I set those wrong date values to null programmatically?
For now, I'm having an AttributeManager on the rejected port where I manually set all the date fields to null, but it's not user friendly and also error-prone as there are many other date fields.

2
  • I think you need the NullAttributeMapper transformer upstream of your date formatter in your workspace docs.safe.com/fme/html/FME-Form-Documentation/FME-Transformers/…
    – Mapperz
    Commented Jan 19 at 21:53
  • I am not going to down vote because I haven't been around in this forum long enough to know the edge cases of what is considered applicable/appropriate, but this strikes me as a non-geospatial question. Sure, a lot of people do geospatial work with FME, but does that make FME geospatial software? Is there no other forum suited for FME non-spatial questions?
    – bixb0012
    Commented Jan 25 at 22:02

3 Answers 3

3

To give you the long story, all invalid dates/datetimes used to be set to null in the output, but that wasn't a great idea because you could never tell what the original value had been. Hence, we no longer do that.

The option to replace with null obviously wasn't included on the new transformer. I suspect the reason is similar. The output from the Rejected port is now the preferred method of dealing with that data.

So, what you are doing with the AttributeManger is probably the intended solution. But I can think of a few other options that might be preferable for you.

  • As Mapperz noted, you could use a NullAttributeMapper transformer to automatically map items to null. But I can see that would be hard to handle in cases where the date is just incorrectly formatted (eg 30-Fecember-2023)

  • Use an AttributeValidator transformer to check whether each attribute is a valid date (or datetime - the two are different) before the DateTimeConverter, and deal with failures accordingly.

  • Use an AttributeManager before the DateTimeConverter to check for valid dates and set them to null if not. You would do that using a Conditional Value, like so:

enter image description here

I think the last two of those work fairly well because you don't have to worry about "many other date fields". You can include all fields, whether they are good or bad. Just process any attribute that is also selected in the DateTimeConverter.

2
  • 2
    Oh, I also filed requests to update the documentation to say what happens when one value fails out of multiple selections, and to create a list of log messages when there are multiple failures (like the AttributeValidator would do). I think we could do better. Commented Jan 22 at 17:55
  • 1
    Good Answer and follow up on documentation. I have again learnt some more insight on FME.
    – Mapperz
    Commented Jan 25 at 22:23
0

As a workaround can you install a FME 2022 version, take a datetimeconverter from 2022 and bring it to your 2023 version(do not update it). It should convert correct datetime and make incorrect as null. Incorrect will still flow through rejected, but with null. Not sure how the spaces will be handled but I suspect it will also be null.

1
  • As written in the question, white spaces are actually not set to NULL; they stay as they are: white spaces. Commented Jan 25 at 21:03
0

WorkbenchCanvas

Above is my setup. I create 3 attributes :

  1. "mydate" is a string
  2. "mydate2" is the current time
  3. "mydate3" are some spaces

DateTimeConverter is version 5.0 (so the one from FME 2023) and DateTimeConverter_2 is version 4.0 (so the one from FME 2022 and not upgraded)

This is the outcome from DateTimeConverter (version 5.0): DateTimeConverter outcome 5.0

This is the outcome from DateTimeConverter (version 4.0): DateTimeConverter outcome 4.0

So spaces are converted to null with the older version

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jan 26 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.