ENH: The XLS_SIGNATURE is too restrictive

[geoffrey-eisenbarth]

Pandas 1.2.4 fails to open XLS files generated by Lotus 1-2-3 because they have a different header than the one expected in XLS_SIGNATURE (io.excel._base line 1001 and then 1050).

I know I'm likely to be the only person in the world with this issue, but my boss still uses Lotus 1-2-3 🙄

I'm willing to submit a pull request with my fix if people are okay with it. The fix basically involves changing XLS_SIGNATURE to a list:

XLS_SIGNATURES = [
  b"\xD0\xCF\x11\xE0\xA1\xB1\xA1\xE1",
  b"\t\x04\x06",
]
ZIP_SIGNATURE = b"PK\x03\x04"
PEEK_SIZE = max(map(len, XLS_SIGNATURES + [ZIP_SIGNATURE]))

and then later:

if any(peek.startswith(signature) for signature in XLS_SIGNATURES):
  return "xls"

I don't know if the second XLS_SIGNATURES value is "good," but it works. I can attach an XLS that was exported by 1-2-3 if it would be beneficial to others, or if they'd be able to help me find a better second value for XLS_SIGNATURES. I tried looking through the byte-code for anything similar to the "DOCFILE" XLS signature, but didn't see anything.

[rhshadrach]

Thanks for the report! An example sheet would be helpful to further diagnose.

[geoffrey-eisenbarth]

@rhshadrach See attached. If you have any suggestions I'd be happy to put together a PR.

PANDAS.XLS

For future reference: OpenOffice has made available the various BIFF files for testing.

[rhshadrach]

Thanks @geoffrey-eisenbarth - documentation is hard to find, but I think this is a BIFF4 format:

http://www.nationalarchives.gov.uk/PRONOM/Format/proFormatSearch.aspx?status=detailReport&id=681&strPageToDisplay=signatures

This agrees with your code: b"\x09\x04\x06\x00\x00\x00" (note I'm replacing your usage of \t). What you suggested would be a welcome PR.

However, it seems likely to me there are other file signatures that will still go unrecognized. One of the shortfalls of the current pandas implementation is that if the user specifies engine but we can't verify the file is correct, we will raise without just trying the engine.

Instead, I think inspect_excel_format should return None if a format cannot be determined instead of raising. Then, we should raise if engine is None with a message to the effect "Excel file format cannot be determined, you must specify an engine manually", and otherwise attempt to use the engine the user specified.

Would you be interested in putting up a PR for this @geoffrey-eisenbarth?

[geoffrey-eisenbarth]

@rhshadrach I agree with your suggested route, and I'd love to submit the PR. Hoping to have time tomorrow to dig more into the code and reference your suggestion with inspect_excel_format. Should be a fast PR.

Thanks for your help!