QST: Pandas read_excel parsing in wrong format

[pathikg]

Research

• I have searched the [pandas] tag on StackOverflow for similar questions.

• I have asked my usage related question on StackOverflow.

Link to question on StackOverflow

https://stackoverflow.com/questions/63067926/how-to-stop-python-auto-date-parsing-while-reading-a-excel-file

Question about pandas

I was facing an issue for some dates in my xlsx file separated by dashes

after doing reading the excel,
pandas converts this dates to ISO format but also interchanges month and day in the format

e.g. for 09-11-2018 (MM-DD-YYYY), ISO format should look like 2018-09-11T00:00:00.000Z but panda converts it into 2018-11-09T00:00:00.000Z which is wrong since 11 is not a month
but nothing happens for the dates in the form MM/DD/YYYY

Debugger output after doing:

claim = pd.read_excel(
    file_name,
    engine='openpyxl',
    sheet_name='claim',
)

why is this happening and how can i resolve this isssue?

I tried to do stop the parsing by using all the suggestions given in the StackOverflow question but none of them worked in my case as well as that question's author's case

[MarcoGorelli]

Hi @pathikg - could you make your example reproducible please?

[vamsi-verma-s]

Hi @pathikg can you check if excel is converting the strings as dates by checking the data type of the cell in excel.

since you are using openpyxl you may also want to just try reading the sheet using openpyxl itself, that way it will be easier to figure out if pandas converts the strings later or not.

[MarcoGorelli]

closing for now, but can reopen if you address #49770 (comment)