Data transformation help required.

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 :frowning: )

#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


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


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


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.


Thanks, that was helpful.


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.