Data transformation help required.

[nikola_tesla]

Hi, I need help regarding following problem, Thanks.

I have a data in rows in following format (router configurations file) I want to convert it into the table. (Note: the number of rows between ! can vary )

#Raw data format

!
Interface 123
Desc ABC
Policy A 12
Policy B 12
!
!
Interface 123
Desc ABC
Policy A 123
Policy B 123
Policy C 123
!

#Required output table format

Interface - Desc - Policy A - Policy B - Policy C

Thanks.

[HansS]

Hi @nikola_tesla

See this wf data_transformations.knwf (58.4 KB). There are 2 challenges in your data. The first one is to split the rows into a header-value and a column value. I splitted the data so it fits your sample data. There is the assumption that there are no whitespaces in your values. The second challenge was to identify the breakpoint of every new records, which is solved by using the Missing Value node in combination with LagColumn node.

gr. Hans

[mlauber71]

@nikola_tesla you could employ this approach marking blocks in the Excel tables

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 Hub or Group Loop Start – KNIME Hub to do something with the blocks (export them delete lines with missing…

[nikola_tesla]

Thanks man, it solved my problem.

I was able to simplify it further,

1. Filtered the “!”, and removed them.
2. Added the row number against the row having “interface”, in rule engine.

Rest was same.

[nikola_tesla]

Thanks, that was helpful.