How do I combine multiple Excel files based on common column names

[Shmelky]

I have multiple Excel files where some fields have the same name across all the files and some don’t/ I would like to create a table where it just takes the common field names across all the files and combines them into one table.
Here is my workflow and settings

Workflow

File list node

Loop start node

I don’t know what setting to use in the Excel reader to tell it to only combine the data based on common field names.

Thank you

[bruno29a]

Hi @Shmelky , you would have to use Column Filter to filter only the columns you want to “combine”, and by “combine”, I am guessing you mean concatenate (append the “same” columns of the Excel files as new rows).

EDIT: I put something together quickly for you, and it looks like this:

I created 3 Excel files as sample:

As you can see, the common columns from the 3 files are “File ID” and “Name”.

In the Column Filter, make sure you focus on the columns you want to include, rather than the ones you want to exclude, because you know that each file will have the columns you want to include, while the ones you want to exclude would be different per file:

And here’s the final result:

Here’s the workflow (The sample Excel files are included in the data folder):
Concatenate same columns of multiple excel files.knwf (33.1 KB)

[Shmelky]

Thank you. So there is nothing that can do this dynamically?

In Alteryx they have an option that you can select where it will combine only the common fields. This would be a great option in Knime so this can be dynamic.

[bruno29a]

Hi @Shmelky , it looks like Knime can also.

The Excel Reader node can read multiple files on its own, without needing loop actually, and it looks like it also have the options to select only common columns. So, essentially, the only thing you need is just this:

You just need to choose Files in folder like this:

And in the Transformation tab, choose Intersection:

There is another option Enforce types. When selected, it will Intersect on both the column name and column type. If you want to intersect only on column names, just uncheck that box.

Results:

And workflow:
Concatenate same columns of multiple excel files.knwf (24.2 KB)