2

I spent last several hours tryng to solve a very simple task - write a dataframe to excel which has a column with datetime.

So I have a dataframe and I have a column with datetime:

df['date'].iloc[800]
datetime.datetime(2020, 5, 5, 9, 10, 8, 280000, tzinfo=tzoffset(None, 14400))

I just want to write it to excel file:

writer = pd.ExcelWriter("UAEArticles.xlsx",
                        engine='xlsxwriter',
                        datetime_format='%m/%d%Y',
                        date_format='%m/%d%Y', options = {'remove_timezone': True})

df.to_excel(writer, sheet_name='Sheet1')

I get error:

ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.

After googling I found dozens of articles, but it seems that everyone is suggesting I need to just remove the timezone. Fine, per documentation:

df['date'].dt.tz_convert()

I still get the error:

ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

What shall I do? I don't care about time component at all, I just need days and I need to write it to excel.

Maksim Khaitovich
  • 4,742
  • 7
  • 39
  • 70
  • The option `remove_timezone` to XlsxWriter should fix this issue. However, based on your warning message you are using a older version of XlsxWriter that didn't support that option. Update your version of XlsxWriter and it should resolve the issue. – jmcnamara May 05 '20 at 09:20

0 Answers0