Skip to main content
Question

Convert different date formats in same attribute

tim_wood
Contributor
Forum|alt.badge.img+8

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
Forum|alt.badge.img

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
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • June 25, 2018
lenaatsafe wrote:

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
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • June 25, 2018

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
Supporter
  • takashiSupporter
  • June 25, 2018

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

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • June 25, 2018

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
Forum|alt.badge.img
tim_wood wrote:
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
  • jacqe
  • May 6, 2019
tim_wood wrote:

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
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • May 7, 2019
jacqe wrote:

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


Helpful Members This Week

Community Stats

31,057
Posts
117,758
Replies
38,831
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.