Hello! Recently we’ve updated depreciated excel reader nodes with the new ones but came across strange behavior and data inconsistency. Only reader has been updated from the below WF:
In the old nodes, we used sheet selection: select first sheet with data. Now this is not possible as we have wrong values. If we will pick first sheet with data, values are incorrect.
We can’t find out what is causing this inconsistency, sometimes also changing start with sheet (random picking) will result in wrong value and node is like “lagged” > means wrong value will remain in node until we do second reset. WF was inherited and Knime recently updated from 4.3.X to 4.7.1. I can provide more details on topic, please let me know. Thank you!
First sheet is BE DEO, BE HCO is 4th. This is exactly my question. How come that data are incorrect when first sheet is selected, but for example if BE HCO data are correct.
I don’t think this is a bug as someone else should already notice this behavior too. We have to doublecheck data every time which is time consuming for simple excel reader task
@hadesian can you just tell the node to use the sheet you want to use by name? You might have to force the use of sheet name like this in some cases (AP-18133):
Maybe you can provide a sample workflow that demonstrates the problem.
Next thing you could do would be to inspect the internal workings of the sheet indexes but that might be too much.
I have exported WF with simplified input: BE_test.xlsx (591.1 KB) test_Reader.knwf (15.4 KB)
If the selection is select first sheet (BE DEO), I get wrong value 2 918 810,51 and selection start with sheet (BE HCO [just example]) value is correct 1 965 448,05. I think some setting is misconfigured.
Logically it makes sense to me that you have to select “sheet with name” because you use a flow variable and overwrite the sheet_name property in your excel reader settings.
Not sure how the other value is exactly calculated
br
I understand the problem with names, but one step before… Are you use always the first sheet? If so, you can set to use the index step, by position just it…
this issue is that values are incorrect. For example value sales 2,9M is wrong and the correct one is 1,8M. This behavior is only when first sheet with data is selected. But if I select manually select some sheet with name, there is correct value. My question is why first sheet with data is not working as expected.
@hadesian you might want to check the hints and examples that have been provided. “BE DEO” is the Sheet with the index number 0 from your example, so this should be loaded.
@hadesian well importing an excel sheet by name is a perfectly reasonable way to do it.
I am wondering about this one. Could you make an example where this ‘wrong’ data gets imported so we have a chance to see if this is about something in the data or in the node.
You could maybe check my example on a different machine. Maybe a private computer.
