Excel data not in correct columns

[cinthy]

Hi,

I have an issue with the excel reader. When I get my file the data is not in the correct column. It seems that the blancs where left out.

this is the original file

How can I get everything as it should be?
cinthy

[takbb]

Hi @cinthy,

Is the data in Excel formatted as a table, or any of the cells “merged”? Also do you know what specific locale/regional setting is being used by Excel.

Also, which version of KNIME and which OS are you using, and which version of Excel?

One other thing, if you save the open the file in Excel and save it under a new name, does the problem persist with the newly saved file?

Finally, are you able to upload a sample spreadsheet that exhibits the problem (make sure to replace anything that is private/confidential)

Edit: This sounds similar to this issue from a few months ago (October 2023). The poster listed it as “bug” but without supplying further information there was nothing for anybody to investigate, so we don’t know if it is actually a bug, or a corruption of the Excel file, but I suspect it would have been related.

Bug: Excel Reader node shifts columns of a few rows

Hi, I have a few Excel tables with headers, but a lot of missing values in some rows. If the data table inside the Excel file is not “formatted as table”, the sometimes Knime does not know which fields belong to the right headers/columns, and it shifts them from right to left. So sometimes I have strings inside of columns where the original excel table has only numbers in it. When you pre-format the excel data “as table”, then the excel reader node reads it correctly. But I think it should …

[Appear3044]

Hi @takbb ,

thank you for replying to our both cases and seeing the parallels. To me it seems like the same issue as in my problem (from October 2023).
It appears on Excel Reader Node which receives data that is not “formatted as table” in Excel before, and has a lot of empty fields in the data.

One solution to overcome this is to pre-format the data as table in Excel beforehand, or just filling the empty fields with some placeholder like “missing”.

PS: I was not able to share my table, as it is sensitive corporate information.

[hotzm]

Hello @cinthy, hello @Appear3044,

a couple of weeks ago, we were made aware of a similar looking problem. It occurred when importing some Excel XLSX workbooks which were exported from PowerBI.
We fixed that problem and the fix is available on the current Nightly build to test. If you want to try it, please use a fresh workspace, as always and if you’re on Windows, use the zip distribution. Testing it should be straight forward since it’s just this one node reading the file .

I would be glad if some of you would try it on their proprietary file to see if it fixes your problem and report back.

Best regards,
Manuel

[Appear3044]

Hi @hotzm, I tested the new nightly build version on Mac vs the old Version on Mac, and in deed, it fixes the problem, thank you very much!

And yes, my file was an xlsx file exported from PowerBI.

[hotzm]

Thanks for trying it out! The bugfix is tracked under the internal ticket number AP-21960 and it will be in all upcoming releases for the versions we still maintain (unfortunately it did not make the cut for 5.1.3 ). So the soonest would be 5.2.2 I think.

[forum]

Internal ticket ID: AP-21960
Fix version(s): 4.6.6, 5.3.0, 4.7.9, 5.2.2, 5.1.4, 4.5.4
Other related topic(s):

Bug: Excel Reader node shifts columns of a few rows

Hi, I have a few Excel tables with headers, but a lot of missing values in some rows. If the data table inside the Excel file is not “formatted as table”, the sometimes Knime does not know which fields belong to the right headers/columns, and it shifts them from right to left. So sometimes I have strings inside of columns where the original excel table has only numbers in it. When you pre-format the excel data “as table”, then the excel reader node reads it correctly. But I think it should …