Hello - I have one excel spreadsheet with three worksheets. For each worksheet I need to remove multiple columns that I don’t need. How can I remove various columns in each of the 3 sheet without losing the selections?
Hi @mb7846,
what do you mean with “without losing the selections”? 
I think in general the new cell updater node should be able to overwrite columns with empty values:
Or you could read in the Excel with the Excel Reader Node - filter the column in Knime - then overwrite the sheet with the filtered knime table
Do you maybe have an example in what you expect?
3 Likes
I lost my [removed column] selections on sheet 1 when I went on to remove columns on sheet 2. When I select sheet 2 and remove columns on transform, it prompts me to reset, when I did that, I lost all of the columns that I removed from sheet 1. Do I need one node per sheet? Further, how can I save a new excel file and save all 3 [seperate] worksheets?
@mb7846 I think we would first have to establish what you and and cannot do with generic Excel functions in KNIME right now
- you can load almost all Excel sheets in various settings (all sheets from one file at once if they have the same structure), loop thru a lot of Excel files, read all sheet names to make decisions about them and so on. So you can import all kinds of dats from Excel
- then you can EXPORT data either by writing a new file or replacing a single sheet (which will be a plain data sheet without any formats)
- recently the Excel Cell updater cam along and now you can place data at a certain position in your sheet
All this would concern data in and out and would not do anything to any advanced excel functions or features. In addition to this you have these options
- use the Continental nodes to format your Excel sheets (Continental Nodes for KNIME — XLS Formatter Nodes | KNIME)
- use external tools like openPyxl and Python to actually manipulate additional Excel functions, like adding named table spaces, insert formulas, ‘operate’ on your sheets (renaming etc. other than deleting and writing them), insert drop-down lists and filters
I think you would have to tell us more about what you actually want to do. Best would be if you could upload a file representing your challenge with the original file and desired outcome. That would make it easier to create a working example.
I have a larger collection about Excel and KNIME you might want to explore:
2 Likes
Thank you for the information. Here is what I’d like to do: Create a workflow that I can run on a daily basis that cleans up an excel data so that I can create pivot charts .
input:
Cleanup - always remove certain columns from the file- in each of its 3 worksheets
Row filters - if certain rows contain a text, remove those rows
Combine- take another sheet another excel file into sheet 1
output: save the new file to do more work on it in excel (pivot charts)
The column filter would be best suited for that task:
After you have done that you could then just reference the result towards your other tables
You could use the dialog oriented
Or write rules of your own with the:
If you have very complicated rules other nodes might also help. You would have to specify the rules.
You can concatenate KNIME tables so that they would then form one table
There is a Pivot node in KNIME - you could then save the results into Excel. Or you could have a pivot table in an Excel sheet and replace the source for the data and then recalculate the results.
There is a node from @AnotherFraudUser (cf. all Excel Utilities) that would allow you to write data into an existing excel file without changing the formats. Although I have not used it much as of yet.
It would allow you to force a recalculation of the values. You could try and see if that would also include an existing Pivot Table in Excel:
And if you would provide us with a minimal example we could further work on that together:
6 Likes
Great, thank you! I will get to work building this workflow as a result of your help 
I can’t help but ask/wonder what the “transformation” tab on the Excel reader node’s purpose is; can it also execute in deselecting columns
1 Like
indeed you could rename columns, drop them or change their types. I would like to do explicit transformations in separate nodes but you could do that. You should make sure that the structure stays the same. You could allow changing structures (and would have to deal with that later) or the Excel Reader node would fail if a file would have a new structure.
1 Like
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.