Retrieve date from filename

Hi all,

I have a workflow which I feed with Excel files. Each file contains data from different weeks, but I need only specific days from each file. The filename reflects this as it contains the calendar week in question: e.g. data_CW25.xlsx.

Currently I use a “Date&Time-based Row Filter” in the workflow to extract these dates manually, but I would like to be more independent and not always have to remember to change the date range in that node, whenever I upload a new file.

Is there a way to extract the exact week (Monday to Sunday) from the filename and use it in my workflow as a filter?

Many Thanks.

Best regards,
g

Hello @gentile,

sure there is. But what should be output from week 25? Two new columns in data with Monday and Sunday data or flow variables or? And how is it defined what is the start and end of week 25?

Br,
Ivan

Hello @ipazin,

It would be great to use the date range of week 25 to filter the data. Maybe as a variable that I can pass to the “Date&Time-based Row Filter”, so that I don’t have to change it manually.

Thanks,
g

Hi @gentile,

variable makes sense in this case. And how is it defined what is the start of week 25? (End if obviously 7 days later.) If you use Create Date&Time Range to create dates for whole year then you can extract week number for each date using Extract Date&Time Fields to get minimum date based on week number from file name but question is does week number calculation in KNIME is the one you need.

Br,
Ivan

Thanks @ipazin

The start of the week is always Monday 00:00 and end is Sunday 23:59.

Would you suggest creating a help table that translates the week numbers to a start and end date and use this as a lookup? Or is there another way?

Plus: Is there a simple way to extract the calendar week from the variable “file_name”? Or do I need to write the filename to a new column and substring the week from there?

Hi @gentile,

If week numbers you get from KNIME are not the ones you need then for sure you need external file holding ween number and start date. Keep in mind you can define locale when extracting week number from date so you might have proper week numbers from KNIME.

If your file name has same structure each time yes. Create a flow variable upon reading data into KNIME from file path/URL and use some variable manipulations node with appropriate logic.

I have created a workflow example which demonstrates above said. Take a look and if any questions feel free to ask

Br,
Ivan

3 Likes

Looks great! Thank you @ipazin!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.