Excel data imported to KNIME that consists of stacked tables with multiple headers throughout

[cameron767]

My data is being imported to knime via an excel reader and the issue I am experiencing is with transforming the imported data into a knime friendly format with one row as headers and data following. The current format has labels and column headers from rows 0-4 then data following. At about row 22 the same labels and column headers are once again listed (for a different client this time) and this takes up another four rows with new data following. The excel sheet is basically stacked tables, each with their own headers. How can I transform this so that the workflow can recognize when one of these 4 row dividers has been reached, take necessary actions in formatting, then repeat the process? The lengths of the data will vary so I cannot hardcode certain row numbers to be removed; however, the columns will always be the same from table to table. Thanks

[mlauber71]

@cameron767 welcome to the knime forum. You can take a look at this example, you identify the start of your tables and mark them as blocks and then import them in a loop

Move head rows to a column

@willy_oracle typically you would mark all lines with a Counter. Then identify the ‘headers’ with a rule engine [IF Col1 LIKE “Head*” => Counter] and keep that (starting) row in a separate column. Then you fill the missings with the previous value so a block would emerge marking the data table: [image] So now you have the blocks marked an can use a Table Row to Variable Loop Start – KNIME Community Hub or Group Loop Start – KNIME Community Hub to do something with the blocks (export them delet…

[takbb]

Hi @cameron767 , if you are using KNIME 5.x, the Table Splitter node could be your friend here too.

Table Splitter

To turn one data table into multiple tables, chain multiple Table Splitter nodes with each being provided the conditions (such as the column headings to look for)