0

I have an XLSB file to convert into CSV in Python but the years are changed from 2022 into 2092 in the CSV file. Would like to receive your help and guidance with the issue. Thank you.

test = pd.read_excel('./date_error.xlsb', engine='pyxlsb', sheet_name='date', skiprows=0)
test = pd.read_csv('./date_error.csv')
test = test.astype({'Date1': 'datetime64[D]',\
                'Date2': 'datetime64[D]'}) 
test.to_csv('./test.csv', index=False)

The file is attached hereto. https://github.com/LiamV9/LiamV9/raw/main/date_error.xlsb

Liam
  • 13
  • 3

1 Answers1

0

Searching around and I found the one that works:

df[['Date']] = df[['Date']].apply(pd.to_numeric, errors='coerce', axis = 1)
df['Date'] = pd.to_datetime(df['Date'], unit='d', origin='1899-12-30')

The other issue that I have some column with check in/out time in decimal format of "0.5998".

With Excel formar converter to Time, it will be Time: 10:30 AM.

How could we automate that in Python?

Liam
  • 13
  • 3