I have 3 datasets that I have exported onto 3 separate sheets with excel, although I would also like a fourth sheet that is a summary page at the beginning that can have 3 different pivot tables from the datasets. The pivots will all contain different column headers, and ideally I would be able to have a pivot table then space, then pivot table, space, etc. Is this possible to get them all onto one sheet? Thanks
Hi,
consider using Excel Writer node and select an option âAppendâ inside. You will most probably need to loop over Excel Writer nodes with each of them writing one of your pivot tables.
Hope it helps!
Best,
Daria
Hello, thanks for your reply. Unfortunately it wonât let me append all the pivots into one sheet as I have to either say fail or overwrite in each of the excel writer nodes that Iâm looping through.
Hi @ORice36 ,
I am not sure what you mean, so if the information below does not help, could you elaborate please?
Excel Writer node has an option âAppendâ (see screenshot below). If you want to use the node for both: writing the table in a separate sheet and append it to the sheet for all tables it will not be possible.
You have to use 1 Writer node to write a table in a separate sheet and another Writer node to append this table to another, âsummaryâ sheet.
Hope it helps.
Best,
Daria
1 Like
I am trying to put 3 pivot tables all into one sheet of a spreadsheet which is why this wouldnât work for me as that appends the separate pivots to different sheets. Thanks
Hi again,
I am addressing the issue of writing different tables to the same excel sheet. In order to do so, you need to connect each of your pivoted tables (I assume you do pivoting with Pivoting node?) to 3 separate Excel Writer nodes and select append in the Sheets section inside (I ). It is possible with Excel Writer node as you may read in description or in the screenshots below
Hope it helps.
Best,
Daria
2 Likes
@ORice36 I think this will only be possible if the Pivot table is defined before and the other sheets will be overwritten by a workflow. You might want to make a plan of what you want to do.
From what I read I do not think you can create a ârealâ Pivot table from Python (some pages claim they do but it does not work).
With the help of OpenPyxl it is possible to add data to an existing Excel sheet.
My excel writer node doesnât include append for the sheets, it just has overwrite and fail. Is that in an update? Thanks
Thanks, my python isnât great atm so this might be something I look to do in the future. Thanks for your help though.
Hi @ORice36 ,
which version of KNIME Analytics Platform do you have? To my knowledge append option for section Sheets has been introduced in version 4.5.0.
You just need to update your version in this case and you will have this option and will be able to follow the solution I suggested.
Best,
Daria
As far as I remember the Excel Sheet appender exists as a separate node in older versions. You might look for that node
br
@Daniel_Weikert , @ORice36 the new Excel Writer can also append sheets to an existing Excel file depending on the settings. âAppendâ in the File settings will mean that the existing file will not be deleted and then the sheet can be overwritten, appended or it could fail if the sheet exists.
For sheet names als this:
Yes thatâs true but if this is not available in his version he probably uses some legacy KNIME version where the append is not part of the writer
br
1 Like
Hello, yes i was using 4.4 version, I am updating now so hopefully should work. Thanks
2 Likes
Hi @ORice36 ,
great. If it works, please, mark my message above as a solution.
Thanks.
Daria
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.