Recognize Headings dynamically

[MarStu]

I am reading multiple excel files inside a loop but the headings to use are often in different rows.

Sometimes they are in row2, sometimes in row3 and so on, however they always start with the same content in column0.

I tried to get it to work with variables and another loop but I just cant get it to work.

Anyone know a way to recognize headings dynamically depending on where they are in the excel sheet?

[ipazin]

Hello @MarStu,

welcome to KNIME Community!

Well if there is no content before headings your heading will always end up in first row if you uncheck option Table contains column names…. Then you promote first row to column header.
See solutions from this topic for it: Promote a Row to be a Column Name

Maybe it would be useful to have an option to have first non empty row as header in Excel Reader…

Br,
Ivan

[mlauber71]

We recently had a similar question. Maybe you could benefit from the solution. There always was a table somwhere on an excel sheet and a keyword to identify the line where the header would start.

Loop on the basis of single Excel sheet data

OK it was slightly more complicated than I initially thought. But I hope it does work now: give each line a unique ID to identify them later identify the ‘heads’ of the tables (“FORMNAME”) like you already did use moving aggregate to give the heads unique numbers bring these numbers back to the data fill the intermediate (empty) cells with the head’s number use a group loop on the blocks identified by the head numbers extract the first line as column names make sure empty columns that would ju…

[MarStu]

Hi @ipazin,

thanks for the welcome.

There is content containing sometimes one, sometimes multiple rows of informations that are not relevant to my work. That means the headings are rarely in row0.

I receive the excel files from colleagues and they are always different. I want to automate the process without having to manually delete the rows I dont need.

[ipazin]

Hi @MarStu,

I see. Then use Row Filter to filter out only row that should be header as content in column0 is always the same and promote it to column names while filtering all rows above it.

Br,
Ivan