Filter Options in Excel Reader

Hi,

I want to filter and get file with element “_Current year & month” > “_2212”

I am unfamiliar with regex and could anyone help to generate regex that fulfil this criteria ?

I’m a little confused.

Filter Options in Excel Reader

“Filter Options in Excel Reader” refers to operations that can be done on file names or extensions.

I want to filter and get file with element “_Current year & month” > “_2212”

What’s an element?
It appears that you want to filter individual records inside a file instead of filtering a subset of files from a larger collection files?

I am unfamiliar with regex and could anyone help to generate regex that fulfil this criteria ?

That’s not really how regex works. It only matches patterns. It can’t do math.

“_Current year & month” > “_2212”

The entity on the right is a string. You can’t do math on a string.

Can you clarify the post a bit?
What exactly and specifically do you want to accomplish?
Can you provide examples of your data along with your desired results?

3 Likes

Hi @elsamuel,

This is not a math, I just wanna show that is it possible to have regex to get "Current year & month” as a filter criteria.
For example, Input:
image

Output: Excel reader read file “Register 2212”

Hope this clarifies

I think I understand better now.

Here’s an overview of a workflow that I think will do what you want:

image

The Date&Time Input node is configured to output the execution time as a flow variable:

image

The String Manipulation (Variable) node uses the execution time flow variable to create the RegEx. The expression I used is

join(".*",substr($${Sexecution_time}$$, 2, 2),substr($${Sexecution_time}$$, 5, 2),".xlsx").

Name the resulting variable something memorable:

In the Excel Reader node, I set up the filter as follows:

First you just need something in the File Name field. I chose an asterix but any character will do.

The next step is important. You’ll need to configure the node to use the RegEx variable you create in the previous node:

2 Likes

@mmngeoh
if you want your regex to work you need to include the “.*” in front and at the end of your 2212
guys please remind me on what to escape to show regex correctly in my answer :wink:
br

@Daniel_Weikert , you need to use the Preformatted Text option that you can use via this icon:
image

2 Likes

@elsamuel

Just be careful when dealing with dates. Different locale will have different format, so positions might vary. It’s always safer to use the Extract Date&Time Fields node where Knime will always get the correct year, month and day regardless of the format used. Longer for sure, but safer and bullet-proof.

1 Like

Thanks for ur help @Daniel_Weikert @elsamuel @bruno29a :blush::blush:

@bruno29a I don’t have column includes month and year, is there any other option that can get date and time input regardless of locations ?

If you list the files and folders (there is a node with that name) and then use Files/Folders Meta Info node it tells you the creation date as info. So maybe you could use that.
br

1 Like

Here are examples

1 Like