Good morning KNIME world, I’m new here and I’m using KNIME for hobby for 1 year to find licensed software alternative solution. I used KNIME successfully for MS/MS data extraction with better result than licensed software. Now I’m trying to do something different.
We have a big excel file we too many data inside (ex. Assigner, Data, TubeID, etc) and I would like to generate another excel file with sheet for every for ex Assigner. I found the way to do it.
The problem is that I need to do this operation every week to have a final .xls with all months data inside.
The problem is that every time I made this operation, it overwrite the previous file instead of append on it.
Do you have any solution for this? There is an Append option on Excel Writer node, but doesn’t work with Sheets. Here you can see the workflow.
Hi Matt,
Currently, the Excel Writer node cannot append data to existing sheets directly. What you can do, however, is in your workflow read the current content of the file and then concatenate it with your new data and then write it into the file.
Kind regards,
Alexander
@Matt9301 could you elaborate on what is not working? If you cannot load all the excel files at once could it be because of different table structures. If so you might have to revert to loops.
I have a collection about Excel and imports. Maybe you can find a scenario you could adapt.
If you could provide a sample of data that cannot be loaded the community might have a look. Of course you should not upload any sensitive data.
My workflow is working to obtain the first file, like this (Sheets for any Assigner): First file.xls (7 KB)
This is good, but if I want to add more data to this file, I can do it manually, but if the same Assigner have more data to add in the same sheet, I cannot use this workflow because every time I start it, it will overwrite the old sheet.
My idea is to be able to attach new data (day by day) just changing the input file.
This is yet not possible when writing data to Excel. However there is ticket to support it and have added +1 there for you (Internal reference: AP-14911). If another file format is an option for you try CSV Writer node which has option to append data to existing file.
Hello @ipazin,
I’m glad to see your message here! So sad to know there is no way to append sheet data with the Excel writer.
I’ll check with CSV Writer node, but I was looking for a way to do it quickly and without extension change.
Hi,
We do have the Excel Sheet Appender (XLS) (deprecated) node that you can use in the same way I described above. You will probably have to use it together with Read Excel Sheet Names. Does that help? From KNIME 4.3 onwards the Excel Writer can also append Excel sheets directly.
Kind regards,
Alexander
Hi,
But then you can read the sheet first and use the concatenate node to add your new data, can’t you?
Then it does not matter that it overwrites the sheet.
Kind regards,
Alexander
@AlexanderFillbrunn sorry for the late answer but I was far from my laptop.
I can read only the first sheet, not the others (is the only problem).
@mlauber71 thank you for your answer! I never used Python on KNIME so I have no idea of this!
I think that the problem is the laptop I’m using. The company doesn’t allow installation of software (Python) not available in their Software Center. Thank you any way for your idea that could be the right one!
Hi,
Maybe I am just missing something here, but the Excel reader has the option to read any sheet in an Excel file. If you don’t know the names, you can use the Read Excel Sheet Names node to list them and then iterate over them with a Table Row to Variable Loop.
Kind regards
Alexander
Hi,
Yes, that is correct. But using KNIME’s looping constructs as described above it is still possible to treat multiple sheets.
Kind regards
Alexander
Hi Matt,
That depends on the problem. If every sheet is treated the same way, no extra nodes are necessary. You can also put everything for a single sheet into a component to make it easily reusable and parameterizable.
Kind regards,
Alexander
