Skip to main content
Question

Different date formats in the same date column


rich90599
Contributor
Forum|alt.badge.img+4

Hello,

I have multiple invoice CSVs I pull in every month with about 300 records each. It eventually goes into a database table that is used as a lookup.

The CSV has an invoice_date column and at first I thought the invoice dates were all the same format (M/D/YYYY >> 2/1/2020), however, some invoices have dates coming in the following format: (YYYY/MM/DD >> 2020/02/01).

I was wondering if there was a way to standardize this so I don't get NULL date values in the translation when the date format I'm looking for isn't recognized. I'm having a hard time doing a condition because both the date type use the "/" character as a separator. If it was a "-" or something then I can use a condition to search out the special character and parse those dates using a particular format. Any help on this matter would be very helpful!

Thank you.

10 replies

redgeographics
Celebrity
Forum|alt.badge.img+49

Ah, long live consistent data :)

 

You can use a StringSearcher to do the parsing. No FME handy right now, so I have to work from memory, but if you use the regex ^\\d{4}/ it should filter out the YYYY/MM/DD ones.


rich90599
Contributor
Forum|alt.badge.img+4
  • Author
  • Contributor
  • May 6, 2020
redgeographics wrote:

Ah, long live consistent data :)

 

You can use a StringSearcher to do the parsing. No FME handy right now, so I have to work from memory, but if you use the regex ^\\d{4}/ it should filter out the YYYY/MM/DD ones.

Ah, that did it! Thank you very much.


ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • May 6, 2020

You can use an attribute as an input format in the datetimeconverter, so you can use an attributecreator with a conditional statement to create an attribute containing the format

Then use this in the datetimeconverter


ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • May 6, 2020

Actually, you don't really need the date time converter at all


rich90599
Contributor
Forum|alt.badge.img+4
  • Author
  • Contributor
  • May 6, 2020
ebygomm wrote:

You can use an attribute as an input format in the datetimeconverter, so you can use an attributecreator with a conditional statement to create an attribute containing the format

Then use this in the datetimeconverter

Ah this is a great work around as well! THank you for your help!


david_r
Celebrity
  • May 7, 2020

For my own education I thought I'd see if it was possible to chain two DateTimeConverters, the second one connected to the <Rejected> port of the first one. The idea being that I'd try to parse the date according to the first format, then retry with the second format if the first failed.

However I notice that the DateTimeConvert sets the date attribute to NULL before it exits the <Rejected> port, even when playing around with the "Repair overflow" setting.

Is this by design, @mark2atsafe? Wouldn't it make more sense to leave the input attribute untouched?


redgeographics
Celebrity
Forum|alt.badge.img+49
david_r wrote:

For my own education I thought I'd see if it was possible to chain two DateTimeConverters, the second one connected to the <Rejected> port of the first one. The idea being that I'd try to parse the date according to the first format, then retry with the second format if the first failed.

However I notice that the DateTimeConvert sets the date attribute to NULL before it exits the <Rejected> port, even when playing around with the "Repair overflow" setting.

Is this by design, @mark2atsafe? Wouldn't it make more sense to leave the input attribute untouched?

I agree, it would make more sense to keep the original attribute intact if it's rejected. Little point in inspecting the rejected feature if the reason for rejection is stripped...


rich90599
Contributor
Forum|alt.badge.img+4
  • Author
  • Contributor
  • May 7, 2020
david_r wrote:

For my own education I thought I'd see if it was possible to chain two DateTimeConverters, the second one connected to the <Rejected> port of the first one. The idea being that I'd try to parse the date according to the first format, then retry with the second format if the first failed.

However I notice that the DateTimeConvert sets the date attribute to NULL before it exits the <Rejected> port, even when playing around with the "Repair overflow" setting.

Is this by design, @mark2atsafe? Wouldn't it make more sense to leave the input attribute untouched?

I attempted this first but that's what I had a hard time figuring out. It was setting all the rejected date records to null which seemed a bit counter intuitive to me. I was able to use the regex method as mentioned in @redgeographics's post and isolate the different date format. I parse the normal date format in the attribute manager and the anomalies in the datetimeconverter.


david_r
Celebrity
  • May 7, 2020
rich90599 wrote:

I attempted this first but that's what I had a hard time figuring out. It was setting all the rejected date records to null which seemed a bit counter intuitive to me. I was able to use the regex method as mentioned in @redgeographics's post and isolate the different date format. I parse the normal date format in the attribute manager and the anomalies in the datetimeconverter.

Thanks for confirming. I've flagged it with Safe for analysis.

Possibly the same issue: https://knowledge.safe.com/questions/91296/datetimeconverter-rejected-port-null.html


mark2atsafe
Safer
Forum|alt.badge.img+44
david_r wrote:

For my own education I thought I'd see if it was possible to chain two DateTimeConverters, the second one connected to the <Rejected> port of the first one. The idea being that I'd try to parse the date according to the first format, then retry with the second format if the first failed.

However I notice that the DateTimeConvert sets the date attribute to NULL before it exits the <Rejected> port, even when playing around with the "Repair overflow" setting.

Is this by design, @mark2atsafe? Wouldn't it make more sense to leave the input attribute untouched?

That's a very good point. I'll check into that.


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