I’m currently experiencing what I think is a very niche issue. I’m in the process of developing a workflow to dynamically create and populate templates within excel. I’m utilizing the copy excel sheet node from the AF Utility Nodes extension. I’ve noticed some finicky behavior that I’m hoping to fix. When executing the workflow an error message will appear (attached below). I’ve experimented using wait nodes and while that seems to lower the frequency of the error, it still occasionally happens. I’ve even tried to run the workflow with NO other applications open/running and still get the error.
I’ve never used Try Catch nodes but I’m thinking that is where my solution lies. I’ve watched a few videos including https://www.youtube.com/watch?v=zlH8W0lWl_I. This is the desired effect I want, the workflow to re-execute where it left off if there is an error. However, I’ve been unsuccessful in my attempts to achieve it. For one, my loop terminates with a flow variable instead of a table so the Variable Condition Loop End node isn’t an option. Number two, my loop starts with a Table Row to Variable Loop Start node, not a generic loop start.
In summary, I want to automatically “re-execute” the variable loop end node if the excel writer node errors out.
If it matters, you could use Variable to Table Row and transform your variable data to table data. Note, that changing table specs could occur which should be handled then (one problem solved, next one showing up )
Edit: another idea: The Excel Writer has been updated a while ago. Maybe instead of using the Copy Excel Sheet node you could do a different approach. Read out the data you want to copy and use the Excel Writer with variables. You need the output file path and the new sheet name to be controlled by a variable.
You get the output file path either by creating a path variable using Create File/Folder Variables or reading an excel file and outputting the selected file path as variable (better).
The list of sheet names could be a table over which you loop using a Table Row to Variable loop.
Do you actually have any of the excel files open in Excel or does the “finicky behavior” (file locked by another process) go away if you write to local drives rather than the network? It might be a timing/latency issue causing the specific error you are getting.
Maybe you can write files to a local folder and then at the end move them all to the required network folder.
@JGebhard I wanted to build a workflow that would do a try catch anyway. It does follow the model and would try to append sheets to an existing Excel file.
In the first instance of the outer loop a _temp.table will be written that would contain the procedures and one could follow if the loop was successfil.
Within the component an inner loop would try 10 times (you could adapt that) to finish the job. A Try Catch block would check if the operation was successful and fill a Flow Variable “success” with “y” or “n”.
The results will be stored in the _temp.table you could later check for the exact point(s) of problems and you might even use it to restart a loop for just the ones who failed.
Thank you all for your responses. I was able to combine bits and pieces of logic from each suggestion/example. I essentially did what you suggested @mlauber71, and created an inner loop with a conditional end.
To address your point @JLD, the only thing that leaves the loop is a flow path that starts another loop responsible for populating the newly created sheets. This is achieved by using an excel cell updater node. The workflow you attached has the exact logic that I’m looking for, but doesn’t work for me due to the formatting of my template file. The read in template has merged cells, borders, highlighted boxes, etc. When using the excel reader node, KNIME removes all of the formatting.
@takbb, you are 100% correct about the issue arising due to writing on the network drive. When executing the workflow to local storage, no issues arise. Unfortunately, I’m building this tool to be deployed and used by other members on my team. It is my goal to have all files from the workflow to be written to our shared network drive.
I’ve attached some updated screenshots of the entire workflow and the solution that I’ve developed, largely based off what Markus provided. The try catch process is actually working perfectly. When the excel writer node errors out due to a latency issue, the loop runs again until a successful output. With that being said, I’m still experiencing some issues, that I’m not sure how to address. Following full completion of the workflow, the excel file is where it should be within our teams folder structure. However, upon opening the file, I get a blank excel background with the message “Microsoft Excel is waiting for another application to complete an OLE action” I’ve done a short bit of research and didn’t find any sort of KNIME documentation fix the issue. What I also noted as strange is I’m unable to delete the file until I restart my computer.
Something must happen to the file during the error and try catch process that either Windows or the network really doesn’t like. I’m not sure if there’s anything I can do in my workflow to mitigate the issue, but I wanted to describe exactly what is happening. Thanks again for any responses/feedback you guys might have.
The question is if it does make sense to directly write to a shared folder. You will always have to be sure the synchronisation is finished and no other process is interfering.
Hi @JGebhard , I agree with @mlauber71 about potentially writing locally and then doing a file move.
However, if that is not an option, I wonder if something here could be useful. This is a workflow I created when I was just starting my KNIME journey
The thing that got me wondering about this workflow is the java snippet:
There’s no guarantee it will work or assist with your use case but I wonder if that could be adapted and built into a loop that keeps checking to see if the file is locked before then progressing? It may achieve nothing more than you already have, but could be worth a shot!
)
