How do I dynamically read multiple files that have a certain word in them and extract as a separate table node

[Shmelky]

I have a folder with multiple files. I would like to loop through and extract each file into a separate node based a specific word that appears in the file name. Files that have that same word should be appended. For example, let’s say I have the following files
inter234ADP_344down.xlsx
jan4ADPexpense.xlsx
Sun_jan_export.xlsx
sept_energy_invoiceexport.xlsx

Here are the keywords I am looking for to extract as separate files: ADP, SUN, ENERGY. I would like to loop through the folder and create 3 separate exports: ADP (has two files that should be appended), SUN, and ENERGY.

Any help or links to solutions would be awesome!

Thanks!

[Mark_Ortmann]

In case those names are known in advance you can use the list files nodes and set the filters accordingly. After that you can use a loop etc…

If you’re reading csv files you could select the files in folder mode and adapt the filters.

[Shmelky]

Ok.
Would the file loop come after the filter since we are not appending all the files together, rather just the files that have the same keyword name in them?

Is there a solution where I can use a list of names that can get refreshed? I am thinking (but not sure how to execute) where I run the list file nodes, then add a field that matches the keyword from the list (there will always be a single matching keyword, not multiple) and then filter? The file names can be long and messy so matching a single keyword would simplify it.

Thanks!

[bruno29a]

Hi @Shmelky , you can take advantage of the fact that the new Excel Reader is able to open multiple Excel files from a folder, and you can also filter what files to open.

Let’s first keep it simple, let’s go for the 3 “categories” you mentioned. I created the 4 sample files from your sample:

And for the sake of having some data to determine what the Excel Reader will read, I created these entries in the files:
inter234ADP_344down.xlsx:

jan4ADPexpense.xlsx:

sept_energy_invoiceexport.xlsx:

Sun_jan_export.xlsx:

You can configure the Excel Reader like this:

You choose the Files in folder, and you can set the Filter Options like this to filter on the ADP files:

Make sure that you do not check the Case Sensitive, and you can see from your own sample, ENERGY is in fact written as energy in your file name, similarly SUN is written as Sun.

With this filter, it already sees 2 files out of the 4 that qualifies for this filter and I can see the data from the 2 files in the Preview:

Similarly for the 2 other Excel Reader nodes:

Now that you have an idea with configuring Excel Reader to read multiple files containing the matching keyword, we can now go a step further and make this list of matching keywords become dynamic where the list of names can be “refreshed”.

You basically can have your list in a table that you can refresh/update as you want, and then loop through the table and call the Excel Reader while passing the data from the name list table as variable to filter:

You can then do your operations after the Excel Reader within the loop (basically between Node 7 and Node 8 in my workflow).

Here’s the workflow: filter files based on filename.knwf (38.0 KB)

EDIT: Note though, if you are going to process different file structures within the loop, that may be more challenging - that’s usually a common issue in anything that we do: create a generic solution and you get a re-usable solution, but you have less flexibility.