XLS file cannot be read

[JulienG]

Hello all,

I am contacting you because I cannot read the xls files in my workflow (excel reader crash). So, at the moment, I receive a xls file every morning and then, I have to open it and save it. After this manipulation, the excel reader will correctly read my xls file.

I have seen that many discussions are already open on this topic, however, I could not really find a suitable solution for my issue. I even tried the R solutions (the only solution proposed on the other discussions) but it did not work neither.

Is there a solution to read the xls file automatically without saving it? Also, I would really apreciate to avoid using a R solution for this.

PS: I would like to share with you the file I receive for more efficiency but this is confidential data. As I don’t know how the file is built, I cannot reproduce the issue.

Thanks for your help!

[ipazin]

Hello @JulienG,

Excel Reader crashes means stops execution with error sign or? Do you see any error message? You can set your log level to DEBUG (File → Preferences → KNIME) and check your log file (...\knime-workspace\.metadata\knime\knime) for more information.

Considering it works as expected when you open it and save it suggests some compatibility issue. If possible can you ask that whoever is creating these Excel files create a dummy one so you can share it?

Br,
Ivan

[JulienG]

Hello Ivan,

Thank you for this very fast anwer.

The error we have is: “Execute failed: Not enough data (0) to read requested (2) bytes”. While for sure, when I open the file we have undreds of lines of data.

However, I will not be able to send you any example file. We receive this document from a customer and we cannot afford to ask this to him.

Thanks again for your help.

Best regards,

Julien

[morpheus]

Hi @JulienG,
are you able to open the excel file with Excel? If not, then the file might be corrupted.

BR

[ipazin]

Hello @JulienG,

The Not enough data (0) to read requested (2) bytes error seems to indicate it’s an old or non-standard file. See here for more:

Reading excel 2003 files with apache POI - FileNotFound

This makes sense as when you open it and save it conversion is done to newer Office version which is then successfully read into KNIME.

To skip manual work you can try following:

• ask customer to provide you with the newer format (I know there is probably no chance for it but at least you can give it a try )
• try reading file with older Excel Reader nodes (there are three deprecated versions on Hub)
• automate Excel conversion (Open → Save) using Java or script which you can call from KNIME
• try reading file with R/Python

Br,
Ivan

[JulienG]

Hello both,

Thank you very much for these very usefull information.

So, I have investigated more on the file and we found out that the file is a very old version of Excel as you both suspected. So, yes we can open it in Excel but not in R or Python. Indeed, the file is a ‘binary’ file which does not correspond to the format expected by Knime, python or R when we want to open it.

So, the next step here will be to ask our customer to change the version of Excel if possible but we suspect a negative answer.

Otherwise, we might try one last solution to automatically open and save it in java or script because using R or python is already tried and was not successfull.

Thank you both,

I wish you a nice Weekend.

Best regards,

Julien

[bruno29a]

Hi @JulienG , another option could be that newer versions of Excel are usually able to open old versions of Excel files. You could then save/convert it as new xls(x) file via Excel itself.

You should then be able to open the new file via the Excel Reader node.

EDIT: Worst case, if the conversion does not happen, open the file, manually copy the data and paste in a new Excel file.