Most efficient way to read folders of excel and constrain by a date range. each excel has the date field.

Question

Hi,

I have a folder structure based on YEAR folders which contain MONTH folders which contain WEEK excel files. The WEEK excel files may contain date ranges which may go outside the month range of days.

After reading all the necessary excels I only want data of a whole month of days - e.g. so all the days in January 2025 say.

What would be the most efficient way to do this? For example there could be days at the start of January 2025 in the folder 2024, folder December.

i’m not too familiar with where clause before and after and whether they would apply to excel data.

Would inline sql be an efficient way?

Or, as in my example, will I have to read in December 2024, January 2025 and February 2025 before filtering to make sure i have all the days of January?

Thanks for any advice.

j.botterill · Answer

I would use the File and Directory path reader, point to a folder with a file extension filter and to recurse into subfolders (under the parameters) https://docs.safe.com/fme/html/FME-Form-Documentation/FME-ReadersWriters/path/path.htm

Use a DateTimeConverter transformer to ensure your date fields are in a standard format, such as the FME format where you have YYYYMM

Then a Tester transformer to filter records for the specific month you want (e.g., all dates in January 2025 could be 202501), regardless of which file or folder they came from.

Most efficient way to read folders of excel and constrain by a date range. each excel has the date field.

1 reply

Community Stats

Latest FME

Community Stats

Latest FME

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded