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.
Thank you for your time!
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.
I would like to thank you, Alexander!
This may be a great solution for this case.
Seems not been solved because Excel Reader is not able to read all sheets inside D:
@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.
@mlauber71 thank you for your answer!
Here you can find an excel starting file!
excelExport (1).xls (27 KB)
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.
Thank you anyway for your time!
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.
Welcome to KNIME Community!
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.
Thank you for your support!
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.
@AlexanderFillbrunn I was not able to see Excel Sheet Appender (XLS) (deprecated) but searching for another workflow, I found it and tried to use it, but same problema, the Appender overwrite old sheets D:
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.
@Matt9301 there is a way to do this with the use of Python pandas and openpyxl
You would have to provide an existing excel file and a sheet name and then sheets with the same structure.
@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!
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.
I see a single sheet read option on my node.
Yes, that is correct. But using KNIME’s looping constructs as described above it is still possible to treat multiple sheets.
Thanks and sorry for the late reply.
Yeah, but every time I have a new sheet inside I must add new nodes to solve the problem.
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.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.