Skip to main content
Solved

Convert character field to date/time in csv

  • February 3, 2023
  • 3 replies
  • 156 views

epro_admin
Contributor
Forum|alt.badge.img+7

Hi FME community,

I am still fairly new to FME. I'm getting stuck with date/time fields in csvs and excel documents. I've attached the field I'm trying to convert into DD/MM/YYYY HH:MM (%d/%m/%Y %h:%m ?), but it writes out empty if I try the DateConverter or AttributeManager> Text editor > DateTimeFormat. It's likely I'm using the transformers incorrectly. Is anyone able to point me in the right direction?

Best answer by j.botterill

Hi epro,

Firstly ensuring the CSV reader separatesimage the columns, confirming the delimiter type in the parameter's

 

So 15/08/2022 10:59:52 a.m. appears to be a custom "Input" format. Expanding the quick reference and using the Preview to see if we have the correct syntax and

image soon find that perhaps the a.m. and p.m. needs a stringreplacer before the Dateconverter?

imageanother string replacer for the pm...

run to the last string replacer and inspect the new date field valuesimageInput format

%d/%m/%Y %H:%M:%S %p

15/08/2022 10:59:52 am

 

 

 

View original
Did this help you find an answer to your question?

3 replies

j.botterill
Influencer
Forum|alt.badge.img+36
  • Influencer
  • Best Answer
  • February 3, 2023

Hi epro,

Firstly ensuring the CSV reader separatesimage the columns, confirming the delimiter type in the parameter's

 

So 15/08/2022 10:59:52 a.m. appears to be a custom "Input" format. Expanding the quick reference and using the Preview to see if we have the correct syntax and

image soon find that perhaps the a.m. and p.m. needs a stringreplacer before the Dateconverter?

imageanother string replacer for the pm...

run to the last string replacer and inspect the new date field valuesimageInput format

%d/%m/%Y %H:%M:%S %p

15/08/2022 10:59:52 am

 

 

 


Forum|alt.badge.img+2
  • February 3, 2023

@epro_admin​ Thanks for including a small sample dataset - that makes it much easier to test a solution.

Just for completeness... The CSV reader has the capability to parse data/time fields, it's buried in the reader parameters:

imageBut... in this case it won't help since there is no way around the 'a.m.' or 'p.m.' format, so you have to use the approach described by @jamesb16otterill​ 


epro_admin
Contributor
Forum|alt.badge.img+7
  • Author
  • Contributor
  • February 7, 2023

Thank you both for your replies!


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