[FIXED] Read_excel(), A column of string, but in excel format, it is date and cause error

Issue

My data.xlsx is something like this

 Number  Grade
3217839     A
7893242     B
3782195     C
      0     D

However, in excel, the Number column text format is "Short Date", which cause the data to become

  Number  Grade
 #######     A
 #######     B
 #######     C
00-01-00     D

I used df = pd.read_csv("data.xlsx"), and it hit error and show NaN

the error (only showing A2):

UserWarning: Cell A2 is marked as a date but the serial value 321783921 is outside the limits for dates. 
The cell will be treated as an error. warn(msg)

     Number Grade
0       NaN     A
1       NaN     B
2       NaN     C
3  00:00:00     D

I have tried with df = pd.read_csv("data.xlsx", coverters={"Number": str}). The result still the same. Any way to solve this without manually changing the format in the excel?

Solution

Excel represents dates with a "serial" number, starting from 1 for 01.01.1900 up to 2958465 for 31.12.9999. So both Pandas and Excel complain about your values because they cannot be interpreted as date. Just change the cell format,it is simply wrong.

Answered By - gimix