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

[Averin7777]

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

[qdmt]

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?

[gonhaddock]

Hello @Averin7777

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

How to remove filename from Path?

Hello @amateur Besides @lelloba solution based in Column Expression node; you can achieve the disclosing of the Path with KNIME base nodes by concatenating ‘Path to String’ and ‘URL to File Path’ nodes. [image] BR P.S. Just for the records; this option can be valid as well: [image]

I hope that you find it helpful.

BR

[Averin7777]

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:

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

[gonhaddock]

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

[Averin7777]

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

[gonhaddock]

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

[Averin7777]

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