Here is the workflow, data and the desired output files attached for reference.
Hope this provide more clarity on my requirement.
Please let me know for any additional information on this.
Data.xlsx (17.5 KB)
Recipient Name.xlsx (22.5 KB)
User Wise Report test.knwf (228.5 KB)
Regards,
Pavan
Hi @pawanmtm,
Not sure what exactly your goal is, but you are writing to the same file and the same sheets in all iterations.
You are not using the path created by the Create File Name node.
Hi Armin,
My aim is to create multiple files based on the create file name node with all the 3 sheets (summary, p2p queries and t&e queries) each.
Since the creation of summary sheet is complicated in KNIME, i have created a template with the excel where KNIME writes the data into P2P Queries and T&E Queries sheets and Summary sheet populate the required data and graph automatically using excel formulas. This template should be saved as with the variable created by the create file name node. This should repeat for all the files.
Hope this helps in understand the requirement better.
Regards,
Pavan
So you have to pass the variable to the flow where it goes to the writer nodes and then set the output file location by using the variable.
Hi Armin,
I was able to do it by using the below workflow. However i am missing the summary sheet.
Regards,
Pavan
Just wondering if someone has any luck on the above requirement.
Regards,
Pavan
Hi,
If I correctly understand your problem you have an excel file like a template for your report Template.xlsx (14.4 KB) and an excel file with some data Data.xlsx (8.8 KB) to process in KNIME. Then the result is written back to the template for every group (in my example users) so you need n files (based on the template) one for each group (User1.xlsx (11.6 KB), User2.xlsx (11.7 KB) and User3.xlsx (11.7 KB).
I have started with making n copies of the template file (one for each group) and then copy the manipulated data into each file (created before) separately as a new sheet based on user and filename.
Look at this workflow for some idea
User report.knwf (102.7 KB)
I hope it will help you in some way
HI there @pawanmtm,
If I got you right you were able to populate 2 sheets for each row and if summary sheet is populated automatically what is troubling you?
As a side node I find your workflow example a bit complicated to figure out. Perhaps next time try with fewer node and simple logic or a step by step approach with multiple topics (it is more topics for same issue but sometimes it is easier). Also you can add your data into workflow folder and use relative paths so reader nodes don’t have to be configured 
See here: Reproducible (Minimal) Workflow Example
Br,
Ivan
Hi @andrejz,
Thanks for your time
You are correct to some extent.
I have the data to process in KNIME. Once it is processed, based on the variable created from create file name node, if the document type is Travel & Expenses, then write the data to T&E Sheet, otherwise write it to P2P Queries sheet. From these 2 sheets, i try to get the volume, value and average using excel formulas and then create graphs out of these numbers.
Then make a copy of the template file and rename (save as in excel term) with the variable.
Similarly all the files has to be created as per the number of users.
I am using the template, so that the summary sheet is updated automatic.
Hi @ipazin,
I don’t see the files created in the output folder, i am not sure where i am missing on this.
I have arranged the workflow step by step with annotation to have better understanding and will follow the same going forward, thanks for sharing the best practice. Since i did not reset the workflow while exporting and i thought other can see the workflow the way i see.
i am thankful to folks who are trying to understand and provide solutions as much as they can, i do try to put across the best way to make it more easy for others to grasp/know. I am sure we are very close and crack it soon.
Regards,
Pavan
Hi All,
Does anyone has any input on the above query.
Regards,
Pavan
Hi @pawanmtm,
So Excel Sheet Appender nodes are successfully but you don’t see files? Then you are writing them to a different location as to one you are looking…
Br,
Ivan
Hi Ivan,
There is no change in the location, but still the files are missing, here is the screen shot of the same.
Regards,
Pavan
Hi @pawanmtm,
can it be that flow variables determining location are not well defined or configured? Don’t think I can help you more unless you share workflow that reproduces this with some (dummy) data…
Br,
Ivan
Hi Ivan,
Please find attached the workflow with relative path for your reference. sorry for the delay in providing the required information.
User Wise Report test.knwf (268.1 KB)
Regards,
Pavan
Hi @pawanmtm,
why do you use Create File Name node? The flow variable created there is not used anywhere. Also I can see Recipient Name.xlsx file created by Excel Sheet Appender nodes. Do you need to create one Excel File per iteration with name created from Create File Name node?
Br,
Ivan
Hi Ivan,
So you mean we do not need Create File Name node, we can take variable names directly from the Table Row To Variable Loop Start node?
Yes, exactly. i need to create one excel file per iteration with the name created from Create File Name node keeping the Recipient Name file structure (1 summary and 2 data sheets).
Regards,
Pavan
one small correction for the above is for each iteration, the data has to be written in Receipient Name file and then rename it to the variable from Table Row To Variable Loop Start.
Regards,
Pavan
Hi @pawanmtm,
I have modified your workflow a bit to write to your template file in each iteration and then using Copy/Move Files to copy Excel file to new location with new name. For it to work I used absolute paths in Create File Name (choose directory where output will be) node and Local File Browser Configuration node (choose where your template file is located). Here is attached workflow. Give it a try and if any questions feel free to ask.
User Wise Report test_ipazin.knwf (443.6 KB)
Br,
Ivan
Hi Ivan,
Finally the output has come as desired. Coincidentally i was also thinking of copy/move files node implementation in the workflow to get the result, however i was missing the use of local file browser, string to URI nodes completely (as i was not aware of their use), hence i was not getting the desired output.
Though it took long time, it made me to think of other options like python/R integration with KNIME for writing data as required (formatting, etc.). Overall it was a good learning.
Thanks for your valuable support @ipazin, @armingrudd, @aworker, @andrejz.
BR,
Pavan
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
