editing excel data table structure (removing rows)

[lriva]

I would need to edit an excel file by going to:

• split the cells that are joined
• remove rows that have sub-tot

Initial table-original file:

End Result (desired)

I don’t know if I have been clear

thanks in advance for the support !

Luigi

[takbb]

Hi @lriva

What does the table look like if you read the file into KNIME using the Excel Reader?

Could you upload a sample Excel file (with any sensitive information replaced), and then people will be better able to help as most likely the required transformations will depend on your specific file.

[takbb]

@lriva, Having a quick play with Excel, with some merged cells, and my guess is that your file will load in something like the following format:

In which case, a Rule-Based Row Filter could remove all rows that contain either “Sub tot” or missing value in the Date column (or alternatively in the Code column, as same rules would apply) and then a Missing Value node could “fill down” the the value of Line from previous row.

e.g.
Rule Based Row Filter:

MISSING $Code$ => FALSE
$Code$="Sub tot" => FALSE
TRUE => TRUE

Missing Value node

After that you can use String to Date&Time node to make the date column into a proper date datatype if that is required, and the other columns can be adjusted to the required date types if they aren’t already using such nodes as String to Number.

[lriva]

thank you !
all perfect
you are fantastic !!