Hi.
I am reading a file using an excel reader in KNIME. It shows me inaccurate results. In “File Content” it shows correct data but in file preview it’s wrong. Excel reader moves some data positions when there are hidden columns (Please check dates). I am using the 4.4 version too. Please look at the screenshot and I am attaching data source
Seg.xlsx (10.5 KB)
@jamalbutt1232 you could try and de-activate the settings for skip empty rows and columns.
Hi @jamalbutt1232,
you have hidden a lot of hidden columns.
As long as you say knime to skip the hidden columns and this probably would not return the correct results.
I suppose to disable all Skip’s to see if it works then.
BR
But I don’t want hidden columns in my result. So it means if the number of hidden columns is big, knime won’t return proper result?
But it is obvious that your data are left shifted if they don’t have entries in column A and B.
One option is to read the whole spreadsheet and afterwards removing all unnecessary columns via column splitter and only keep your column of interest.
BR
You are right @jamalbutt1232 , it is having problems when handling hidden columns in that when columns are hidden, it appears to be mistaking empty cells as part of those hidden columns, and then left shifting everything beyond the hidden columns to fill the empty space.
If you put spaces in A1 and B1, you can see it then gets those columns in row 1 correct, but then lower down, you still get left shifting with other empty cells, so my guess is that empty cells and hidden columns don’t work well together.
I just tried this in Knime 4.3, and it did the same there too. It’s not great, and it feels like a bug, or at least a limitation of the Excel Reader. It is certainly counter-intuitive and I cannot think of a use case where this would be the desirable or expected behaviour.
It looks like the solution at the moment is to tell KNIME not to ignore hidden columns, and then to either untick the columns you don’t want in the “transformation” tab, which is a little tedious as I don’t think you can mark whole blocks, or as @morpheus has alluded to, attach a column filter node afterwards to remove the columns you don’t want.
Unless anybody else knows an alternative option/combination of options that we haven’t spotted I think that is probably the pragmatic solution at the moment.
Hi @jamalbutt1232 @mlauber71 @morpheus @takbb , it seems to work properly on my side, and with default settings.
Preview screen:
Reader results:
Settings:
I’m running on an old system though (and with old Excel - don’t know if the installed Excel matters or not): Windows 8.1 with Excel 2010
EDIT: The only weird thing that I see is that my Knime sees the last columns all as “AC”
Hey @jamalbutt1232,
which 4.4 version are you using?
In the latest bugfix releases (4.4.2 and 4.3.4) we fixed an issue where rows/columns where moved due to hidden columns.
If you do not want to update, try to check the Reevaluate formulas option. I assume this will fix the issue as well (at least it did when I tried it out).
Best regards,
Julian
Sorry, I forgot to mention that I’m on Knime 4.4.2. So based on what @julian.bunzel said, it could be that it worked for me because of the Knime I’m using, and not because I’m on Windows 8.1 (with Excel 2010).
Thanks @julian.bunzel and @bruno29a, looks like neither my knime 4.3 or 4.4 installs of knime are quite at the latest version, so that’s good to know it’s fixed… and I’ll update mine soonish.
I went looking for a mention of this behaviour before posting but didn’t find anything, but clearly somebody spotted it already! 
Incidentally I also had that issue with the end columns all being given AC prefix.
Well, I am using the 4.4.1 version. I guess the issue is with this specific version for now. I will update it to the latest one and check if it works or not. Thanks, everyone though for your suggestions! @julian.bunzel @bruno29a @takbb
The issue is resolved as I updated it to 4.4.2 verion. Thank you, everyone!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
