Excel inputting two data sets on separate sheets

[Toby76]

Dear Knime Forum,
hereby I’d like to ask for your kind support.

I built a Cost Center charge out work flow as attached. (upper section)
My challenge is to combine two excel data sets into one file on two separate sheets.

In the section"Creating Charge out files by Cost Center" the workflow creates several cost center workbooks with a sheet including the summary by cost category but I also want to add to the same workbook, on a separate sheet, all the according cost center line items.
Summary of line items by Cost Category is grouped by the Groupby node.Cost Center Charge out.knwf (107.8 KB) Cost Center Charge out.knwf (107.8 KB)

It would be fantastic if you could help me to figure out the way to have both, cost category grouped items and line items on two sheets within the same cost center workbook.

Thanking you in advance!

Best regards,
Tobias

[mlauber71]

@Toby76 welcome to the KNIME forum. Without having studied your workflow … have you considered using the

Excel Sheet Appender (XLS)

So you could add several sheets to your target Excel file?

[Toby76]

@MLauber71: Thanks a lot for taking you time to look at my issue. In fact I did consider the Excel Sheet Appender node. My issue was that I did not find the solution that with each iteration of the loop by Cost Center, the Excel file could be updated on one sheet with the summary and on another sheet with the according line items…

[mlauber71]

Your example does not contain any data so it is not easy to interprete.

If you want to collect lines and add them at the bottom of your sheet that is not possible with standard Excel nodes. You would have to collect the data first or revert to some Python things with openpyxl - but that might be overengineered.

To collect data you could ‘append’ them to a KNIME table like in this example:

Multiple excels files with multiple sheets

You could use a combination of loops to extract the necessary informations. The File Meta Info node could provide you with information about when the file was last saved if you do not want to use the file name itself. [image] If you want to explore further options how to deal with Excel import you might want to have a look at this collection:

Or you could use the recursive loop

Recursive Loop Start (deprecated)

[Toby76]

Hello! Basically what it should do is to write the data set output of groupby node on one sheet (What it is currently doing with this workflow) and on another sheet ,in the same Excel file, the data set as it would be without application of the GroupBy node for the according cost center. I wonder how I can coordinate/set this up with looping. I wonder if that is somehow possible.

[beginner]

Not an actual answer but my personal rule of thumbs:

• avoid loops as much as possible (they are very slow within KNIME)
• If most of your workflow consists of loops, you have to rethink the workflow

Loops are a choice of last resort.

[Daniel_Weikert]

Not completely sure without having the data and try for myself but if you create the group loop and create the file name then you should be able to save the output location to a variable and if so could you maybe extend your workflow and use the excel sheet appender (as proposed by mlauber71) at the end to open the file using the variable as path and then append the data?
Just guessing

[Toby76]

@mlauber71 and Daniel_Weikert. Thank you both very much for your hints. Based on your feedback I figured out the set up by adding the Excel Appender node and setting the file location as flow variable. As shown.
Again, many thanks!

Br,
Tobias