read_excel na_values replacement after parse_dates

[nylocx]

### Not working
pd.read_excel(
    "test.xlsx",
    na_values={"Test": ['#', 0]},
    parse_dates=["Test"],
    date_parser=lambda x: pd.to_datetime(x, format="%Y-%m-%d"),
)
### Working
pd.read_csv(
    "test.txt",
    na_values={"Test": ['#', 0]},
    parse_dates=["Test"],
    date_parser=lambda x: pd.to_datetime(x, format="%Y-%m-%d"),
)

read_excel behaves different from read_csv in replacing NA values and parsing dates.

Test case:
The test.txt and test.xlsx contain the same data, just one column with header "Test" and 5 entries where 0 and # both represent NA values.

Test
2012-10-01
0
2015-05-15
#
2017-09-09

The first one crashes while trying to parse a "#" character as date.

[WillAyd]

Can you provide the sample files?

[nylocx]

Sure, sorry that I forgot this.
I couldn't upload the CSV with a .csv extension because github prevents me from doing so.
(We don’t support that file type.
with a GIF, JPEG, JPG, PNG, DOCX, GZ, LOG, PDF, PPTX, TXT, XLSX or ZIP.)
So its a test.txt instead. I will edit my report to reflect this.

test.txt
test.xlsx

[WillAyd]

Sorry just seeing this now but thanks for providing the sample files. That does indeed look buggy.

Investigation and PRs are always welcome