Hi Everyone! My issue in general is that I’m adding 2 columns to every tab in my Excel spreadsheet (Record External ID, Record External Source). Each of these rely on data unique to each tab, but I want to merge these updates to the single “original” spreadsheet. I’m using the String Manipulation to concatenate the values and add the new columns for each tab, however, I’m not quite sure how to merge it all together again to the single file. I was thinking of using Excel Appender but wasn’t sure if there was a better way to execute the merge.
Hi @mvaldes
I’m not sure I get it correctly. Could you explain a bit more about your setup? Could you maybe share a workflow?
So how I understand it right now, I would collect all columns in KNIME and then write it as an Excel file?
1 Like
@Alice_Krebs Sure. In the excel file I attached, you can see that I have different tabs for different pieces of information that I manually enter. My workflow does some manipulation using particular tabs to generate a new tab called “sample plan”. The issue I’m running into is that not all tabs are used in the manipulations so I don’t have a single data output that I can simply send to the file download node. I began trying out concatenating all the tabs together (after adding the new columns and using the excel sheet appender), but I feel that has to be a better way. I tried exporting my workflow but I’m not sure which file to upload to here. I’ve attached some screen grabs for the time being. You’ll see in those that I’m independently reader each sheet (concat.png),
hte output before the new columns (beforenewcolumns.png), and then the output after the new columns (afternewcolumns.png). Do you have a suggestion on how to streamline that process or how to bring them all together to a single downloadable excel file?
Hi @mvaldes
Sorry, gotta ask more questions. So you want to add columns in some of the tabs, and then replace these tabs in the existing excel sheet? I assume in every “line” in your screenshot you manipulate one tab?
If the Excel sheets (=tabs) you want to manipulate have all the same formatting/structure, and you want to do the same manipulation to all of them, you could use a loop. The Read Excel Sheet Names nodes would allow you to first get all Excel sheet names, then use more nodes to select the Excel sheets (=tabs) you want to modify and send them as a flow variable to the Excel reader in a loop.
However I am still confused about the “Sample Plan”…
@Alice_Krebs I don’t necessarily want to replace them. That was just my idea for a solution. You are correct, each branch is to manipulate each tab in the spreadsheet.
The issue with the loop is that the tab’s new column values will not be consistent across the entire spreadsheet. The new column’s values are dependent on the unique data within each tab. I ultimately want to make these identical columns in each tab, but have the values in those columns be unique to each tab and have those updates applied to the excel spreadsheet that can be downloadable as a single file.
@mvaldes But if the manipulation you do to the data is all the same, looping works.
I attached you a workflow that would create TestExcelAppend.xlsx from TestExcelAppendInput.xlsx
Probably I have a different KNIME version like you, so you need to adjust some nodes/settings.
We have a book that you might find helpful: From Excel to KNIME | KNIME
and there are also nice videos on Youtube. Hope that helps!
TestExcelAppend.xlsx (10.3 KB)
TestExcelAppendInput.xlsx (10.8 KB)
ExcelAppender.knwf (67.6 KB)
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.