I have a KNIME workflow which essentially splits an Excel file into 4 output tables. I want to loop across 100s of these Excel files so that the contents are appended to the 4 output tables respectively.
I can’t use the ‘Files in folder’ option for Excel Reader as there is some logic that needs to be applied file-by-file in order to extract the relevant information.
So, I’ve used a List Files node to generate the files, then a Table Row to Variable Loop Start to generate the variable as an input to the Excel Reader node. If I create a Loop End to one of the output tables, that works fine. Unfortunately, if I create another Loop End to the other 3 output tables, the loop never ends basically.
One crude method is to replicate the entire workflow 4 times so that it produces each output table in its own separate loop, but I’m hoping there’s a more elegant solution to this. Any help on this is much appreciated, thank you.
If that’s the reason why you can’t use the “Files in folder” option, then similarly you can’t use a loop. Whatever you do within the loop would apply to any file that goes through the loop, just like whatever you would do with the “Files in folder” option would apply to all the files in folder.
So, I’m not sure how you are able to do this via List Files/Folders node + Loop, but not with “Files in folder”.
Can you may be show us what you are doing so we can understand what’s happening?
Thank you for replying. When using ‘Files in Folder’, the output of the Excel Reader appears to be a single output with all the files appended after one another. The logic I’m applying is to perform a search for a keyword and to extract the information based on that keyword. If the output is appended, the search only finds the first instance of that keyword and thus, the rest of the files won’t have their information extracted.
If I use List Files/Folders + Loop, then the keyword search happens file by file and it works but only for one of the 4 outputs.
Without seeing the workflow it’s probably difficult to grasp so I agree with @bruno29a
Can’t you split the flow inside the loop filter and write your different output tables in the loop and then close it with one loop end? (using merge variable node or sth like this)
br
Hope the image pasted OK. Basically, the Excel Reader node should be a list of similar Excel files. The top three nodes (Java Snippet, Missing Value, Table Row to Variable) does a search for 4 particular keywords and converts the row position of these into variables.
The 4 Row Filters extract the relevant information based on those variable positions and does some transformation to produce 4 output tables.
If I use the File in Folder option in Excel Reader, it appends the files. Thus, the top 3 nodes will only find the first position of those keywords (i.e. only for the first Excel file).
If I use the List Files + Loop, then it will loop the top 3 nodes for each file, generating a new extract each time. My issue is that I want to generate it for the 4 output files.
Hope that clarifies it further. Thank you very much for all your help.
So I stumbled on the solution, and it is painfully simple. You can add collector ports to the Loop End node! So I added four collector ports to my Loop End node and linked my output tables to each respective collector port. The Loop End node will then have four output ports, each showing the collected results for each of those individual tables.
Thank you to everyone who has responded to my query.
