Hi guys,
I want to create workflow, which append new sheets to existing workflow. I tried to do it like that:
and in Excel Writer set “append” in write option and “append” in sheets, but this workflow did not execute on KNIME Server. What did I do wrong? How is the simpliest solution?
Hi @pkng,
Could you please check where the job failed on the KNIME Server and which error was raised? You can inspect the Job using the Remote Workflow Editor/Job View, the error will be shown if you hover over the red cross.
In addition it would be good to know: Where is the Excel File stored, within the workflow, on the KNIME Server or on a network share?
Could you mention your KNIME Server and Analytics Platform versions as well?
Thanks,
Michael
I created this workflow
This is my settings for Excel Writer node:
It works on WebPortal, but when I tried again and again new sheets for this Excel does not append. I want to know how I should create this workflow so that I can add a sheet to an existing Excel.
And I want that it will be possible on KNIME WebPortal, so person who execute workflow on KNIME WebPortal will be able to download Excel with append new sheet of data.
Judging from the screenshot you provided you are already using KNIME Analytics Platform 4.5.x which introduced the ability to append data to existing data sheets. On versions <4.5.0 you only have the possibility to add new sheets to an existing Excel file.
I quickly created two workflows for version 4.4. (overwriting the old sheet) and 4.5. (appending to the sheet). Please have a look how the path is configured and how the flow variable is populated with the full path via the “Flow Variables” tab (file_selection → path).
Although my example doesn’t use flow variables to set the file name it could be adjusted easily to your needs (as only file names have to be set, not the whole path).
Hope this helps you,
Best,
Michael
Excel_Writer_Example_4.4.knwf (312.9 KB)
Excel_Writer_Example_4.5.knwf (915.2 KB)
Thank you for your examples. So I understand that user, which execute this workflow on WebPortal should be able to download file with added new sheet to the Excel file. For example if user execute workflow 5th time he should receive Excel file with five sheets. Do I understand correctly?
I tried to put your workflow in my workflow, and sheets to Excel were not appended, I downloaded every time excel with one sheet.
This was based on a misunderstanding. My example covers the use case to append data to an existing sheet (in the 4.5. version of the workflow).
If you want to create a new sheet on every run you have to use a flow variable and set the sheet name via this. The flow variable can be populated with a sheet name using e.g. the date&time creation node.
I adjusted the example to provide a new sheet on each execution.
You can use any created sheet names which matches the excel sheet name conventions.
Best,
Michael
Excel_Writer_Example_with_multiple_sheets_4.5.knwf (15.4 KB)
When I tried execute this workflow on KNIME WebPortal and download generated file, the downloaded file didn’t contain new sheet - it cointained just two sheets and when I executed again and again new sheets weren’t appended. I want that new sheet will be appended after every execution. For example XYZ executes this workflow third time and has three sheets in file which he can download. I don’t know that it is possible in this way. Maybe I must type path where Excel file exists and there new sheet will be appended?
path_append_new_sheets 1.knwf (21.2 KB)
In attachment I sent my example. I want create something like that. It works on KNIME Analytics Platform, but when I tried to execute it on KNIME WebPortal I received information that “workflow not executed”.
I want that person, who executes this workflow, has to type path where excel file is located on her/his computer and type name of new sheet and this sheet will be added to her Excel.
append_sheets_2.knwf (66.4 KB)
I tried to prepare shomething like this too. I thought that I should upload file to temp dir and append new sheet to this file in temp dir and then download this changed file, but sheets did not append to this excel file too. How should I modify it?
Thanks in advance for response.
Hi @pkng,
in your workflow, only one file is available and therefore only one Excel file with one sheet is delivered at the end.
The local version and the server version are to be considered differently.
In the local version, the setting “Delete temp folder on reset” in the Create Temp Folder is taken into account and ensures that the generated Excel file is deleted. This would mean that there would be no file available to insert a sheet into on a restart. In addition, each time this node is executed, a unique directory is created and thus always results to a new Excel file.
In the server version this setting is not considered at all and a temporary file is always deleted. This is because a workflow on a server is first copied to the executor’s working directory and then executed. At the end of the job, this working copy is deleted and also the temporary directory within the workflow.
One solution would be to upload two files to join them together and make them available as a result.
Best Regards
Andrew
In component I have download widget.
I tried connect two files each other and it works if I upload file with one sheet. If I upload file with more than one sheets second sheet is replace by output of table creator. How can I deal with it? For example if user upload file with three sheets I want that my table creator append next sheet to the file.
Thanks in advance for response
Hi @pkng,
I refer to your workflow “append_sheets_2.knf” from yesterday. With the node “Excel Reader (XLS)” only one sheet is loaded and therefore only one table is available at the output of the “Generic File Upload” component. This is then written with the table from the “Table Creator” in an Excel table. Only two sheets exists in your Excel file.
A solution would be not to load a single or with a loop several sheets into tables, but to copy the selected Excel file into a temporary directory in the workflow data area, for example with a “Copy/Move Files” Node . Then expand this file with the Excel Writer and the table from your “Table Creator”. Another sheet is added to the existing file and the existing sheets.
Best Regards
Andrew
I tried create something like this
append_sheets_3.knwf (87.4 KB)
Okay, I created workflow which I attached. It works, but it is not what I need. I noted that generic file upload take first sheet from my excel (for example uploaded Excel had two sheets and this node takes ONLY first) and it is save in Excel Writer node. When I download xlsx file from WebPortal always I have two sheets - the first which was written from my Generic File Upload and the new from Table Creator.
Your solution could be in the “Generic File Upload” component.
To understand: The Excel Reader only reads one sheet into one table. You can see this in the settings. Here you can select the sheet. As an example, I have selected an excel file with three sheets, which have the names “sheet1” to “sheet3”. In the default setting the first sheet is loaded with data, in the example it would be “sheet1”. You can also select the sheets directly, but only one sheet at a time.
So the question is, what the “Generic File Upload” is allowed to deliver as result. In your case, the component always returns only one table, either from the CSV Reader, the Table Reader or the Excel Reader. But always only one table. More is not possible.
If the “Generic File Upload” can deliver an excel file, which then also contains several sheets, you have to move the creation of an excel file into the component. Then the component will not provide one table at the output, but a path to the temporarily created excel file.
A possible solution would be inside the component.
In the case of the CSV Reader node and the Table Reader node: Create a temporary folder. Save the loaded table data with an Excel Writer into this temp folder. This will create an excel file with one sheet. The component provides the path as result.
In the case of an excel file: Use the “file-upload-input (Path)” from “Local File Browser Configuration” or “File Upload Widget” as path for the temporary excel file.
The component provides this path as result.
Best regards
Andrew
Thank you very much for you help! Now it finally works 
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
