• EN
Question

Convert different date formats in same attribute

  • 22 June 2018
  • 8 replies
  • 23 views
tim_wood
Rank
Badge +7

I'm reading in data from multiple XML files, all of which have the same date attributes (xml_char in the XML Reader). However the dates are not all the same format - some are 2018-06-22 and some are 22/06/2018. I want to write the dates to date fields in a File Geodatabase.

I can have 2 DateTimeConverters (one for one date format and one for the other) based on whether the date attribute contains - or / and use a Tester or TestFilter to send the features to the appropriate converter. But is there a better way?

I'm wary of using "Auto detect" in the DateTimeConverter - people have previously said it's better to specify a specific Input Format.

8 replies

L
Badge

Hi @tim_wood

 

Auto detect is now very reliable, however it will only parse dates in either FME or ISO format, i.e. 2018-06-22 will be parsed successfully as ISO date while 22/06/2018 will be rejected as neither ISO nor FME format.

For your scenario, I would suggest chaining two DateTimeConverters. The first one could use either Auto detect or %Y-%m-%d explicitly. The second transformer would processed all features that were rejected by the first DateTimeConverter and parse them with %d/%m/%Y.

 

tim_wood
Rank
Badge +7

Hi @tim_wood

 

Auto detect is now very reliable, however it will only parse dates in either FME or ISO format, i.e. 2018-06-22 will be parsed successfully as ISO date while 22/06/2018 will be rejected as neither ISO nor FME format.

For your scenario, I would suggest chaining two DateTimeConverters. The first one could use either Auto detect or %Y-%m-%d explicitly. The second transformer would processed all features that were rejected by the first DateTimeConverter and parse them with %d/%m/%Y.

 

Great idea @LenaAtSafe but the features coming out of the Rejected port of the first DateTimeConverter have had the date attribute values replaced with NULL. So the second DateTimeConverter just rejects all the features it gets. This happens whether I use Auto Detect or specify a format. So it's back to the TestFilter for now.

 

tim_wood
Rank
Badge +7

Some of my input files are missing one of the date attributes, causing these features to be rejected by the DateTimeConverter. I think specific Null/empty/missing support is required so I've up voted this idea, because you can't assume everything coming out of the rejected port is simply a null value:

https://knowledge.safe.com/content/idea/62374/add-null-support-in-datetimeconverter.html

I'm going to try @arnold_bijlsma's idea in that topic and use @DateTimeParse in an AttributeManager instead.

takashi
Rank
Userlevel 3
Badge +17

This conditional value setting in the AttributeCreator or the AttributeManager is available.

tim_wood
Rank
Badge +7

Hi @takashi I was just going to post the same thing but I've been a bit more specific - using a regular expression courtesy of regexlib.com

L
Badge
Great idea @LenaAtSafe but the features coming out of the Rejected port of the first DateTimeConverter have had the date attribute values replaced with NULL. So the second DateTimeConverter just rejects all the features it gets. This happens whether I use Auto Detect or specify a format. So it's back to the TestFilter for now.

 

You are right! Sorry, I missed that. So, I guess, any solution with @DateTimeParse() then.

 

J

Some of my input files are missing one of the date attributes, causing these features to be rejected by the DateTimeConverter. I think specific Null/empty/missing support is required so I've up voted this idea, because you can't assume everything coming out of the rejected port is simply a null value:

https://knowledge.safe.com/content/idea/62374/add-null-support-in-datetimeconverter.html

I'm going to try @arnold_bijlsma's idea in that topic and use @DateTimeParse in an AttributeManager instead.

hi @tim_wood I'm also facing the same problem as you are. how did you resolve this and if you don't mind could you share your workspace with me? Thanks in advance.

tim_wood
Rank
Badge +7

hi @tim_wood I'm also facing the same problem as you are. how did you resolve this and if you don't mind could you share your workspace with me? Thanks in advance.

Hi @jacqe I used an AttributeCreator with Regular Expressions in the Conditional values section as shown in my answer above (currently just below the original question). Here's an expanded screenshot...

Reply


Community Stats

28,848
Posts
109,912
Replies
36,653
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.