Icon

kn_​forum_​26510_​excel_​single_​file_​loop

Extract a group of smaller tables (identifyed by a keyword) from Excel file with some empty columns and store them in separate new Excel files
Extract a group of smaller tables (identifyed by a keyword) from Excel file with some empty columns and store them in separate new Excel fileshttps://forum.knime.com/t/loop-on-the-basis-of-single-excel-sheet-data/26510/7?u=mlauber71 Book1.xlsxhead_start$Col0$ LIKE "*FORMNAME*" => 1new_idkeep onlyFORMNAMElinesnumbertheFORNAMElinesNode 7sort by new_idfill valuesof group_variableSTARTtranspose headercheck noof columnsfirst row asheadinsert first lineas column namesleavenew_idandgroup_variablealone^(?!new_id|group_variable$).*group_variableENDknime://knime.workflow/data/extract_<>.xlsxv_excel_namecreate a file nameand locationfilter columns withall missing valuesunique_idunique_id Excel Reader (XLS) Rule Engine Java Snippet(simple) Rule-basedRow Filter Moving Aggregation Joiner Sorter Missing Value Group Loop Start Transpose Extract TableDimension Row Splitter Table Columnto Variable Insert ColumnHeader Column Filter Column Rename Variable Loop End Excel SheetAppender (XLS) Java EditVariable (simple) Missing ValueColumn Filter RowID ConstantValue Column Extract a group of smaller tables (identifyed by a keyword) from Excel file with some empty columns and store them in separate new Excel fileshttps://forum.knime.com/t/loop-on-the-basis-of-single-excel-sheet-data/26510/7?u=mlauber71 Book1.xlsxhead_start$Col0$ LIKE "*FORMNAME*" => 1new_idkeep onlyFORMNAMElinesnumbertheFORNAMElinesNode 7sort by new_idfill valuesof group_variableSTARTtranspose headercheck noof columnsfirst row asheadinsert first lineas column namesleavenew_idandgroup_variablealone^(?!new_id|group_variable$).*group_variableENDknime://knime.workflow/data/extract_<>.xlsxv_excel_namecreate a file nameand locationfilter columns withall missing valuesunique_idunique_id Excel Reader (XLS) Rule Engine Java Snippet(simple) Rule-basedRow Filter Moving Aggregation Joiner Sorter Missing Value Group Loop Start Transpose Extract TableDimension Row Splitter Table Columnto Variable Insert ColumnHeader Column Filter Column Rename Variable Loop End Excel SheetAppender (XLS) Java EditVariable (simple) Missing ValueColumn Filter RowID ConstantValue Column

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: m_lauber
Created at: 2020-09-03
On NodePit since: 2020-09-04
Last update: 2024-04-27
Created with KNIME version: v4.2.1
Tags: excelsplittablesextractheadgrouploop

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

Try NodePit Runner!