How to handle merged cells importing from Excel

[buuvinhpro]

Hello all,

I am importing data from an excel sheet and there are cells which were merged, e.g. A5-A6-A7 being merged with value “Year 2017”. When I import to KNIME, it only retains A7 = “Year 2017” and A5, A6 being missing values.

How can I retained the same value for the other 2 cells A5 and A6 after read by KNIME?

Thanks.

Best regards,
Vinh

[armingrudd]

Hi @buuvinhpro and welcome to the KNIME community forum,

You can use the Missing Value node the “Next Value” method for the column to replace the missing values for A5 and A6 with “Year 2017” (or any other missing values with the next available value).

[buuvinhpro]

Hi @armingrudd, thanks for your reply.

Actually I frame my question wrongly…I have a merged cell for A5-B5-C5 (same row, spanning three or more columns), then when I import it retains only A5 and leave B5 & C5 missing…

Your solution actually solves for missing value in the same column (thanks for that btw ). Do you know if there is a node to handle missing value for adjacent cells in the same row?

Cheers.

[ipazin]

Hi there @buuvinhpro,

@buuvinhpro

Do you know if there is a node to handle missing value for adjacent cells in the same row?

Not really. You could probably develop this logic using one Rule Engine node for each column that might be missing due to merging but it may be too complex depending on your Excel file.

What you could try is Transpose your data and then apply Missing Value node.

Br,
Ivan

[armingrudd]

Hi @buuvinhpro,

Sorry for the delay. To fill the column B and C with the value in column A in case they are missing, you can use the Column Expressions node with a simple if-else statement for each column:
E.g.

if(isMissing(column("B"))) column("A")
else column("B")

[mwiegand]

I think what @buuvinhpro is referring to is that cells containing formulas are not imported properly. In this case you’ve got to tick the checkbox “Reevaluate formulas …”. Did I understood it correct @buuvinhpro?

Edit: Screenshot was missing …

[buuvinhpro]

Hi @ipazin,

This is exactly what I am looking for. Actually Transpose solves my problem

Thanks a lot for your help.

Cheers,
Vinh

[buuvinhpro]

Hi @mw,

No I was looking for merged cells that were merged by users in the source files. Reply from @ipazin solved my problems

Cheers,
Vinh