Windows failures are related, will be investigating.

I believe this would also replace #38456.

I find the desire to support xlrd 1.2 at a time when pandas users are already upgrading a package to be both surprising and disappointing, given the potential security issues and poor parsing experience associated with sticking with xlrd 1.2.

This PR has code that is more complex than #38522, even ignoring that which is in place to support the convoluted deprecation process it advocates for. I haven't seen code coverage metrics as part of the pandas PR process, but are you sure all the new conditional branches you've introduced are covered by sufficient tests?

In case it wasn't clear in #38522, I believe the most robust approach in this area is to:

• obtain the stream we're going to end up with anyway
• peek the minimum number of bytes from it to do content-based inference
• .seek(0) to get the stream back to its initial state.

Thanks for the comments @cjw296, I was able to simplify/improve a lot from them.

@rhshadrach - I'm confused as to why you didn't just start with #38522 and add the fallback logic you're advocating for. (corrected)

@cjw296

I'm confused as to why you didn't just start with #38424 and add the fallback logic you're advocating for.

I think you mean your PRs #38456/#38522. I had started this work 7 days ago, prior to the existence of them.

2 linux tests failed to start, all other checks passed. Rerunning.

/azp run

Update: Failure if from one of the builds using zh_CN.utf8

Failure is related, though I don't understand it:

>               handle = open(handle, ioargs.mode)
E               FileNotFoundError: [Errno 2] 没有那个文件或目录: 'foo.xlsm'

[snip]

with pytest.raises(FileNotFoundError, match="No such file or directory"):
>           pd.read_excel(bad_file)
E           AssertionError: Regex pattern 'No such file or directory' does not match "[Errno 2] 没有那个文件或目录: 'foo.xlsm'".

Regardless of which PR was started first, it's also an option to update #38522 to use the behaviour we want (fallback to xlrd if openpyxl is not installed, for xlrd < 2.0, see also #38522 (review), I think it should be fairly straightforward to add that to that PR)

Regardless of which PR was started first, it's also an option to update #38522 to use the behaviour we want (fallback to xlrd if openpyxl is not installed, for xlrd < 2.0, see also #38522 (review), I think it should be fairly straightforward to add that to that PR)

ok sure. let's try to get this in today and cut the release. i view better inference as good but shouldn't hold this up.

@jreback @jorisvandenbossche Is there room for improving the inference done here? As far as I can tell, the inference done here is that of #38522 but also handles bytes and urls (which I believe is causing failures in #38522).

Two of the travis builds failed to start, the third one passed. Restarted manually.

@rhshadrach

https://dev.azure.com/pandas-dev/pandas/_build/results?buildId=50726&view=logs&j=404760ec-14d3-5d48-e580-13034792878f&t=f81e4cc8-d61a-5fb8-36be-36768e5c561a looks like a legit failure

@jreback - Agreed, it is, but I don't understand it. The warning is emitted when openpyxl is not installed but xlrd < 2.0 is and a non-xls file is used. Replicating this in my environment, the test passes with the warning emitted with the right stacklevel (checked manually). So maybe this is a Windows vs Linux issue?

What really doesn't make sense is that actual_warning.filename is pandas\io\excel\_base.py where the Warning is emitted, but the stacklevel is very clearly being set to either be 2 or 4. I don't see how it's possible that _base.py is then the filename.

Attempting to debug via CI now.

If it only fails on windows, I think it is also fine to add a check_stacklevel=False to the particular test ..

If it only fails on windows, I think it is also fine to add a check_stacklevel=False to the particular test ..

Will do, is this a known issue with Windows? Couldn't find an issue on github, will add one if it is.

Actually, it's a test asserting warnings about positional arguments, not about the engine. So that might interfere with the test, because it now also raises another warning than the one it is testing?

@jorisvandenbossche Yes, that appears to me to be the reason why it's failing now. However, it passes locally for me and stepping through the assert code, I don't see any reason why it might not work on Windows. But most likely I'm missing some peculiarity about windows..

I think it is certainly fine to skip the check for stacklevel here (or mayble only skip it on windows, so it's still tested generally)

@rhshadrach sorry, I merged #38456, which will give some merge conflict pains. I can also deal with it if you like

@jorisvandenbossche - Not a problem. The merge was fairly straightforward, but your review would be appreciated to make sure I didn't mess any of it up. (I am also double checking it now)

The stacklevel issue was a bug in one of my previous PRs where the file path was hardcoded using '/'. Should be fixed now. Also your requested test has been added.

Doc changes look good after the merge!

@rhshadrach unfortunately a couple of legit looking failures. note happy to xfail those tests for those versions is fine.

@jreback - the test suggested by @jorisvandenbossche detected that we were not defaulting to pyxlsb for xlsb files when engine is None; opened #38667 and xfailed the test.

@jreback - the test suggested by @jorisvandenbossche detected that we were not defaulting to pyxlsb for xlsb files when engine is None; opened #38667 and xfailed the test.

excellent, ping on green-ish

thanks @rhshadrach amazing job here.

and thank you @cjw296 for your PR and the input.

@meeseeksdev backport 1.2.x

Thanks @rhshadrach !