Challenges with new XLS reader

[knimediger]

As there is a new XLS Reader Node available with 4.3 I was starting to exchange the old nodes with the new one.
But I’m facing some challenges as some internals and behaviour of the node did change:

1. The new node does not allow me to define the filename with a variable. When hitting the variable button I can’ select any of my existing variables. All I can do is to create a new variable.
   What’s wrong with my approach?
2. The old xls reader node did use a column naming scheme with “Col1, Col2, Col3, …”. That was different to the EXCEL one. So I assume that due to this difference the new column naming scheme is now as in EXCEL “A, B, C, …”. But now I’m facing the challenge that I need to rework all my work flows based on the xls Reader Node to adjust the new column names. That’s more than nasty.
   Is there either a change to adapt the column naming scheme back to “Col1, Col2, …”?
   Or do some tools exist which help me to find and adapt all occurences of the column names?

[SimonS]

Hi @knimediger,

@knimediger

The new node does not allow me to define the filename with a variable. When hitting the variable button I can’ select any of my existing variables. All I can do is to create a new variable.
What’s wrong with my approach?

With version 4.3 a new variable type “Path” has been introduced. You need such a variable to control the Excel Reader node. Take a look at the String to Path (Variable) node.

@knimediger

The old xls reader node did use a column naming scheme with “Col1, Col2, Col3, …”. That was different to the EXCEL one. So I assume that due to this difference the new column naming scheme is now as in EXCEL “A, B, C, …”. But now I’m facing the challenge that I need to rework all my work flows based on the xls Reader Node to adjust the new column names. That’s more than nasty.
Is there either a change to adapt the column naming scheme back to “Col1, Col2, …”?
Or do some tools exist which help me to find and adapt all occurences of the column names?

No there is not right now, sorry for the trouble. We are working on adding an option to control the naming of columns. You can use a dictionary to replace the columns names, see this workflow.

Best,
Simon

[knimediger]

@SimonS

That helped to solve my challenge using variables.

Working with the new XLS reader node I have identified another quite nasty issue: Values read from the new readers are different than form the old one. That makes it difficult in using the new node with “old” data as the results are different.

Is there an “improvement” also in terms of accuracy of the values.?
Does a workaround exist to handle that issue?

[SimonS]

Hi @knimediger,

In which extent they are different? I guess you mean numeric values. Try to play with the “Use 15 digits precision” option in the Advanced Settings tab.

Best,
Simon

[knimediger]

@SimonS

I guess you mean numeric values

Hello @SimonS,

You are right. I’m talking about numeric values. Apologise that I did not mention that clearly. The values from the old and new node differ significantly.
Some trials to disable the “15 digits precision” did not change this behaviour.
Are there differences in handling (calculated) values?

[SimonS]

@knimediger I guess the numbers you are now getting are more precise than the old ones, correct? Previously, the numbers were output with the precision that Excel displays by default (i.e. with the default Excel number format). Many users complained about this behavior as precision got lost (Excel actually stores these values with a higher precision). If you want less precision, you could use the Round Double node afterwards.

Best,
Simon

[knimediger]

@SimonS

I did in parallel some more investigations and I confirm that this is the root cause for the different values.
When “increasing” the number of digits in the xls sheet I’ve found the same value as in the new xls reader node.
At first glance this was not really obvious.