1

I am reading an Excel spreadsheet which has a column containing date values using FME.

The problem is that all date value are encoded as normal text in Excel, except a few cells which are encoded as true date format. But when I open the .xlsx file, they all looks the same:

28.03.2010
11.05.2011
31.12.2020

Here for example, the format of the second cell is 'date', while the two others are encoded as 'text'.

I'm using an XLSXR reader in FME. For that column, the reader tells me it is has a 'Char' type:

XLSXR Reader

But once the file is read, these data becomes:

28.03.2010
20110511
31.12.2020

which is causing me some troubles in some following transformer, e.g. the DateTimeConverter.

Is there a way to actually force the reading of that date column as 'text' everywhere? Because it seems that FME is taking into account the actual Excel encoding, even so it is saying its type is 'Char'...

1

When you load the Excel file into FME Desktop - there is a step to set the parameters. You can change the type there to date date/time.

A screenshot will help.

enter image description here

FME version used 2021.0.0.0 (20210305 - Build 21302 - WIN64)

2
  • So if I understand correctly, this is only possible once, during the reader creation right? Once it's there, you can no more change the data type? I've tried your solution. And indeed, the type is not date. But I can always see the differences in the date format between the ones which were encoded as text and the one which was a true date in Excel. :/ (in between, I manage to filter out theses dates inside an AttributeManager by using the @DateTimeParse() function and manually specifying each of the encountered input formats (which also means I have to know them all...). – swiss_knight May 7 at 14:48
  • correct on first load it is available. Your workflow sounds correct downstream, from the FME World Fair FME 2021.1. and 2022 have better type handling and attribute manipulation. – Mapperz May 7 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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