Icon

Combining multiple sheets 1

Examples of combining multiple tables / files into an Excel sheet based on joins

Execute this for quickaccess to WorkflowData folderNode 6Node 8Node 9Node 10Node 11Node 12Node 13Node 14Node 15Node 16Node 17Node 18Node 19Node 25TableA (1)TableB (2)TableC(3)TableD(4)MainTableLoop through table numbers4 iterationsUse iteration number+ 1to define inputfile nameNeeded here as example, just to fetch workflow absolute pathRead TableN.csvget Path locationfor fileKeep just IDand Match columnCollectcolumnswe are collecting IDon each iteration as we need to be able to join it back later, butit does mean we willhave superfluous "ID" columnsfrom each iterationKeep only IDand Match column(Not Main table columns)Join resultantcolumns back to Main TableKeep only first ID columnand "Match" columnsEmpty table (in case CSV read fileswith file not existing)Define Matchcolumn nameas Match NRename Matchcolumnto Match NEnsure rowsalwayspresent inID orderEnsurerowidisordered sameas IDor we will get misaligned rows laterOpen File or Folder Joiner Joiner Joiner Joiner Column Rename(Regex) Column Filter DuplicateRow Filter Column Filter DuplicateRow Filter DuplicateRow Filter Column Filter DuplicateRow Filter Column Filter CSV Reader CSV Reader CSV Reader CSV Reader CSV Reader Excel Writer CSV Reader Counting Loop Start String Manipulation(Variable) Extract ContextProperties CSV Reader String to Path(Variable) Column Filter Loop End (ColumnAppend) Joiner DuplicateRow Filter Joiner Column Filter Try (VariablePorts) Catch Errors(Data Ports) Table Creator String Manipulation(Variable) Column Rename Excel Writer Sorter RowID Execute this for quickaccess to WorkflowData folderNode 6Node 8Node 9Node 10Node 11Node 12Node 13Node 14Node 15Node 16Node 17Node 18Node 19Node 25TableA (1)TableB (2)TableC(3)TableD(4)MainTableLoop through table numbers4 iterationsUse iteration number+ 1to define inputfile nameNeeded here as example, just to fetch workflow absolute pathRead TableN.csvget Path locationfor fileKeep just IDand Match columnCollectcolumnswe are collecting IDon each iteration as we need to be able to join it back later, butit does mean we willhave superfluous "ID" columnsfrom each iterationKeep only IDand Match column(Not Main table columns)Join resultantcolumns back to Main TableKeep only first ID columnand "Match" columnsEmpty table (in case CSV read fileswith file not existing)Define Matchcolumn nameas Match NRename Matchcolumnto Match NEnsure rowsalwayspresent inID orderEnsurerowidisordered sameas IDor we will get misaligned rows laterOpen File or Folder Joiner Joiner Joiner Joiner Column Rename(Regex) Column Filter DuplicateRow Filter Column Filter DuplicateRow Filter DuplicateRow Filter Column Filter DuplicateRow Filter Column Filter CSV Reader CSV Reader CSV Reader CSV Reader CSV Reader Excel Writer CSV Reader Counting Loop Start String Manipulation(Variable) Extract ContextProperties CSV Reader String to Path(Variable) Column Filter Loop End (ColumnAppend) Joiner DuplicateRow Filter Joiner Column Filter Try (VariablePorts) Catch Errors(Data Ports) Table Creator String Manipulation(Variable) Column Rename Excel Writer Sorter RowID

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: bbate
Created at: 2022-11-24
On NodePit since: 2022-11-25
Last update: 2024-11-24
Created with KNIME version: v4.6.3

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!