Merging Multiple Excel Files and Adding File Name to Rows

I have a bunch of Excel files I need to merge. They are all formatted similarly, so I have been able to successfully merge the files using the Excel Reader and Excel Writer nodes. I am very new to Knime, so where I am struggling is adding the file name of each file to the merged data file so for each row I know what company is tied to that data.

Any thoughts on what workflows I should consider to facilitate this?

My file names look something like this:
CompanyName - 47d947f4-cc79-4dd1-8e57-587ce07ae0a8_Q26.xlsx

@kmarrs901 you might add a path or name of the file as a column with a constant value

Thank You. I will try this. I am reading a specific sheet in the workbook. Do I need to configure Read Excel Sheet Names in a specific way?

Hi @kmarrs901

You shouldn’t need such a complicated script to just get the filename as you originally described. In the Excel Reader node, navigate to the Advanced Settings page and enable the option to add a Path Column.

Next, in a Column Expression node use getFileName(column("Path"))

After this, it will be clear from which source file the data originates. In this case, a file for company A or company B

4 Likes

Thank you! That was what I was looking for!

2 Likes