Workflow - Join and append columns from different files in loop - Help

[palbera]

Hi there,

i am sure there is a more easy way to accomplish the workflow that i created:

As you can see i would like to join and append within a starting file a specific column from each excel files in a loop.

Is there anyone that can help me please?

Thanks

[armingrudd]

Hi @palbera and welcome to the KNIME forum,

Yes, there is a better approach.

Step 1: List Excel files
If all of them are in a single folder, use the List Files node to list all files. If not create the list in the Table Creator node.

Step 2: Create a loop to read files
Here, it is very important to clarify if you are joining tables based on some column(s) or it is just a simple column appending.

2-1: If you just need to append the new table to the previous one
Use Table Row To Variable Loop Start and Loop End (Column Append) nodes to create the loop construct. The list table would be the input for the loop start.

2-2: If you need to join tables based on column
Use Recursive Loop Start and Recursive Loop End node to create the loop construct. The first Excel file would be the input for the loop start (And should be filtered from the list).

Step 3: Read Excel files
Use the Excel Reader (XLS) node, and set the location from the list you have.
In case of 2-1, they get converted to flow variables one by one in each loop.
In case 2-2, you have to convert them to flow variables one by one in each loop iteration by yourself.

For both cases, if your tables have column headers, set the corresponding option in the Excel Reader (XLS) node configuration and also check “Disable Preview”.

Step 4: Final step
In case 2-1 you have nothing to do. Just execute the loop.
In case 2-2, use the Joiner node, top port comes from the Loop Start node, bottom port from the Excel Reader which reads the files from the list. You have to split the list and include a single file based on the current iteration number to feed the first Excel Reader node. The output of the Joiner node goes to both input ports of the Recursive Loop End node. In the configurations of the Recursive Loop End check the option to collect data from the last iteration only.