How can I add data to an existing excel file?

I have a work flow taking data and cleaning it. I need to add the cleaned data to a specific Excel file. I can’t just export the data as a new Excel, it has to be added to that specific file.

Attached is the raw data, workflow and the excel template file I need to populate. You can see where the data should go in the Template file (starting at row 3).

Raw data:
MP2 Energy_Reflective.xlsx (10.2 KB)

Template:
!PaymentTemplate.xlsx (11.5 KB)

Workflow:
KNIME_ExcelAppend.knwf (24.2 KB)

Thank you

What difficulty have you been having?

Have you tried reading in the template, concatenating the processed data, then using the Excel Writer to overwrite the template?

image

This is the output of the concatenate node:

2 Likes

The template needs to look exactly the same meaning the blank row at the top as row 1 (asides for what you see in cells A1, B1) and the “headers” on line 2 and then the data directly underneath stating in row 3 (the template file I uploaded had dummy data in rows 3,4,5…, so I cleared that out before reading it into Knime)

Hello @Shmelky,

currently Excel Reader node doesn’t feature such a possibility. But there is ticket to have it and I have added +1 to it. (Internal Reference: AP-14911)

For now you can try following approaches:

Br,
Ivan

2 Likes

@ipazin could you give a +1 from me as well? :slight_smile:

Done @AnotherFraudUser.
Ivan

1 Like

@Shmelky
Not sure whether my idea will solve your problem completly.

My idea would be to keep the headers in a static KNIME table. Then do all required calculation from all of your input.
As last step make a “union” of the header table and the data table using the Concatenate Node Concatenate — NodePit
Please keep in mind that the columns in both tables must be equal…

The already mentioned Continental Nodes allow you to do some formatting in the table if required.

At the end just write the output to xls.

It depends on further use of the output (do you use it only for viewing / printing / do you apply some math to the table / …) this might be a solution.

Hello @Shmelky,

Maybe it can be done if you don’t rename the columns right away.
It’s simpler to append your data if you keep the columns as A, B, C to join data later. :
Result :


(PS: I shrank the columns to make the image bigger, but data are okay).

Check it and tell me if it’s what you are looking for :
KNIME_ExcelAppend_modified.knwf (43.5 KB)

Hope that help you.

Br,
Samir

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.