I find that this workflow solution created by Kathryn is fantastic however, how can one overcome the scenario of the top level branch being an empty table?
I have a few workflows that require an excel output as well as up to 6 sheets appended.
If the top branch that creates the excel file is an empty table, then the file won’t be created. What I would like is that the 2nd branch down would then create an excel file rather than append, and so on.
I can see how this could be done, but the method I am thinking of would require a very large workflow to cover off each and every level of failing, right up to the point of all branches except the last being empty. So essentially the workflow grows bigger for each sheet
Thoughts on perhaps a neat solution (if there is one)?
the Excel Sheet Appender creates a new Excel file if it doesn’t exist. So you do not need to use an Excel Writer first to create the file. This should make it already easier for you.
With KNIME 4.3 we made this clearer by enhancing the new Excel Writer and thus making the Excel Sheet Appender obsolete. The new Excel Writer allows you to append new sheets to an already existing file. In addition the node allows you to specify as many input tables as you need e.g. 6 in your case via the dynamic ports feature. However if the node encounters an empty table it will still create the Excel sheet with the column names but no data. Is this what you would expect or would you rather like that empty input tables would be ignored and thus no sheet created?
Thanks for the prompt reply Tobias, that is of great help!!
Unfortunately we aren’t on version 4.3 quite yet, but I will get in contact with my internal IT to get that sorted (I’m guessing the server will need updating as well) as updates like that certainly make things clearer.
Regarding your last point, I would expect it to still write the table with the headers but no data so that’s not an issue (I can probably work out how to do it if not)
Hello knightyboy,
yes you will need to update the server as well. The production ready release of KNIME 4.3 is also coming out soon.
If you just want to give it a try you can also install KNIME 4.3 in parallel to an existing installation without interfering with it. You only need a directory where you can write to. Simply use the self-extracting archive version from the download section which will copy all needed files into the directory when executed. Once you start the new version point it to a new workspace and you are good to go.
Regarding your last point, I would expect it to still write the table with the headers but no data so that’s not an issue (I can probably work out how to do it if not)
This is exactly what the node does right now
However I have create a feature request to add an additional option to the node to let the user decide if empty tables should be ignored all together. We only need to figure out what to do if all input tables are empty: Create an empty Excel file, create no file (but issue warning) or fail. Any suggestions?
Thanks for your valuable feedback.
On your last point, I actually think creating an empty excel file would be the way forwards.
One could use a switch to decide whether they want to go ahead and export that file or end the workflow beforehand.
For myself, we have 2 scenarios where we end a workflow with writing to excel vs a DB
File is required to be sent to another user in the company on a periodic basis (e.g. control report checking for data input errors). In this case, the smart thing to do would be, if there is no data, you would use a switch to send out the email with a different wording to state that there are no errors to report currently (saves the user opening a file with no data in and getting confused)
Data is required to be written to a file on the local network/cloud storage. In this case, if you do not have the file name controlled by a variable (e.g. adding the date of execution), you will be overwriting the previous file each time. You would want to create an empty table here so that when you access the file, you aren’t seeing old data.
I can see that not all users may immediately realise that utilising switches is the best way to achieve the first scenario, but I’m not sure how publishing a warning message/failing really helps anyone personally.
Excel files in the most part are created to be shared with users that don’t use KNIME/Power BI or any other tool. It is therefore up to the workflow creator to decide what they want to do when there is no data, and how to communicate that with the end user.
