Filename as column where constant value column has a names of the files

Hello, I need to process 195 xls. files. Each file has the name of the week. I need to insert constant value column based on the name of the file. Need to this for each file and concatenate them.
I have got this list files:

And to get this for each file based on the file name:

I quite lost how to do it :neutral_face: :neutral_face:

Hey @Averin7777, are you just looking for this?

If so, in the Column Expressions node, you can use:
removeChars(regexReplace(column("Path"), ".+\\\\([^\\\\]+\\.xlsx)", "$1"), ")")

If you don’t want the .xlsx, you can use:

removeChars(regexReplace(column("Path"), ".+\\\\([^\\\\]+\\.xlsx)", "$1"), ".xlsx)")

Node settings:

Does that help you?

2 Likes

Hello @Averin7777

Alternatively to @qdmt proposed Column Expressions regex approach. You can test this combination of nodes as showed in the next post:

I hope that you find it helpful.

BR

Hello qdmt, thanks fo reply, I tried but I do not now how to create table creator,
Actully better understanding what I need:
this is my file folder:


as you can see each file has a name of week.
and I need to add column value with the name of the file (week) to the each row in each file and concatenate all 195 files
result like this:
image
image

of course one way is to put each file in Excel reader nod + Constant Value column for each 195 files and concatenate them afterwards :sweat_smile: :ok_man: :sweat_smile: but I can avoid this, hope some smart solution exist :pray: :pray:

Hello @Averin7777
If I understood correctly. You have the option in the ‘Excel Reader’ node >> ‘Advance Settings’ tab >> ‘Append path column’. Then, you will get the Path already posted on every row.

Afterwards you can follow the two nodes flow showed in my previous post:

BR

3 Likes

Hello, yes, but I can not see which Node is connected with excel reader with red one above?
Thanks!

Hi @Averin7777
Forget about, I just build it on an open project, using an existent resource, as a sketch. The Excel reader can read ‘files in folder’ without the need of any intermediate node; as showed in the first capture.

BR

2 Likes

Seems it works, thank you very much! I always learn something new in KNIME from you :+1: :clap: :clap:

2 Likes

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