Merging two excel rows to one rows

Hi all,

I am trying to create KNIME table from merged excel file

Excel
price|quality
men_clothing | women_clothing | men_clothing | women_clothing

Need to create KNIME table as
price_men_clothing | price_women_clothing | quality_men_clothing | quality_women_clothing

So far I have been formatting excel and then loading data to KNIME, but now I have been given over 100 excel files to work with.

Please, I need help, any help. I’m desperate.Problem File.xlsx (12.6 KB)

Hi,
just read the file as it is with the Excel Reader (XLS). It will have one row with “price | ? | quality | ?” and another with men_clothing | women_clothing | men_clothing | women_clothing. Now use Transpose to turn the table around. Next use Missing Value on the column that is called “Row0” and choose "Previous Value as the replacement strategy. Now you can use String Manipulation to create the desired column name by using the join-function in the expression. Filter out the other columns using a Column Filter, turn the newly created column into the row id using RowID and finally transpose again to get your correctly named columns.
Kind regards
Alexander

1 Like

Thank you Mr Alex. Instead of last transpose i used Insert Column Header . Worked like a charm.

2 Likes

Hi there @katak_ds,

alternatively upon reading chose option that table contains column names in first row. Then use Column Expressions node with 1 expressions for each column:

join(columnNames()[0], “_”, column(0))

join(columnNames()[0], “_”, column(1))

join(columnNames()[2], “_”, column(2))

join(columnNames()[2], “_”, column(3))

Br,
Ivan

2 Likes

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