python: convert pywintyptes.datetime to datetime.datetime

Question

I am using pywin32 to read/write to an Excel file. I have some dates in Excel, stored in format yyyy-mm-dd hh:mm:ss. I would like to import those into Python as datetime.datetime objects. Here is the line of code I started with:

prior_datetime = datetime.strptime(excel_ws.Cells(2, 4).Value, '%Y-%m-%d %H:%M:%S')

That didn't work. I got the error:

strptime() argument 1 must be str, not pywintypes.datetime

I tried casting it to a string, like so:

prior_datetime = datetime.strptime(str(excel_ws.Cells(2, 4).Value), '%Y-%m-%d %H:%M:%S')

That didn't work either. I got the error:

ValueError: unconverted data remains: +00:00

So then I tried something a little different:

prior_datetime = datetime.fromtimestamp(int(excel_ws.Cells(2, 4).Value))

Still no luck. Error:

TypeError: a float is required.

Casting to a float didn't help. Nor integer. (Hey, I was desperate at this point.)

I might be looking in the wrong plce, but I'm having a terrible time finding any good documentation on pywin32 in general or pywintypes or pywintypes.datetime in particular.

Any help?

Answer 1

I think you were quite close with the datetime.datetime.fromtimestamp. Taking that approach all the way, you could transform your pywintypes.datetime object to a timestamp using its timestamp method. To be safe with time zones, also use the tzinfo attribute. See In [4]: below for the full syntax.

I just ran into the same issue when trying to make a pd.DataFrame out of a few rows of an Excel book. I kept getting this terrible Python has stopped working" dialog box.

In [1]: pywindt
Out[1]: pywintypes.datetime(2018, 9, 13, 14, 2, 24, tzinfo=TimeZoneInfo('GMT Standard Time', True))

In [2]: str(pywindt)
Out[2]: '2018-09-13 14:02:24+00:00'

In [3]: # Conversion takes place here!

In [4]: dt = datetime.datetime.fromtimestamp(
   ...:     timestamp=pywindt.timestamp(),
   ...:     tz=pywindt.tzinfo
   ...: )

In [5]: dt
Out[5]: datetime.datetime(2018, 9, 13, 14, 2, 24, tzinfo=TimeZoneInfo('GMT Standard Time', True))

In [6]: str(dt)
Out[6]: '2018-09-13 14:02:24+00:00'

As a follow up, if you need to check whether or not a cell value is a pywintypes datetime, the following should be good enough.

In [7]: import pywintypes

In [8]: isinstance(pywindt, pywintypes.TimeType)
Out[8]: True

In [9]: # just out of curiousity

In [10]: isinstance(dt, pywintypes.TimeType)
Out[10]: False

Answer 2

Pandas has a similar solution using pd.Timestamp()

Insert the pywintype.datetime object as the argument and set unit='s' (for Seconds, or enter whatever unit the timestamp is in).

For a pandas Series:

def convert(time):

    return pd.Timestamp(time.timestamp(), unit = 's')


newSeries = oldSeries.apply(convert)

Answer 3

Adding a simple option for converting pywintypes.datetime to datetime.datetime

By adding any datetime.datetime type to the pywintypes.datetime will result in a cast to the datetime.dateime type. This can be done using a zero-delta for example.

For the case of the original question, the below can be used without requiring additional modules

desired_datetime_type = excel_ws.Cells(2, 4).Value + datetime.timedelta(0)

Answer 4

You can try something like this

prior_datetime = datetime.strptime((str(excel_ws.Cells(2, 4).Value)).rstrip("+00:00"), '%Y-%m-%d %H:%M:%S')

Answer 5

I see a lot of suggestions to use rstrip. I found that when the time ends in 00 that will not work.

>>> oltime='2020-06-21 19:50:00+00:00'
>>> str(oltime).rstrip("+00:00").strip()
'2020-06-21 19:5'

I sugest using replace instead

>>> str(oltime).replace('+00:00', '')
'2020-06-21 19:50:00'
>>> 

This is because rstrip is removing all instaces of each charature starting from the left end of the string and working to the right:

>>> str(oltime).rstrip('+0:')
'2020-06-21 19:5'
>>> 

Answer 6

In the answer from Alex above, if you remove the space between the %S and %z, with python 3.8.3rc1 and datetime it works:

>>> import datetime
>>> datetime.datetime.strptime("2016-04-01 17:29:25+00:00", '%Y-%m-%d %H:%M:%S%z')
datetime.datetime(2016, 4, 1, 17, 29, 25, tzinfo=datetime.timezone.utc)

Answer 7

If the .rstrip of the timezone feels too gross you I have solution with regular expressions below.

Should be able to handle if a timezone actually is included in the returned number format!

from datetime import datetime
import re
def xldatetime_to_datetime(xl_datetime):
        """
        takes the str() value of a  pywintypes.datetime object and 
        converts it to a datetime.datetime object via regular expression
        """
        p = re.compile('(?<=\d{4}(-\d{2}){2}\s\d{2}(:\d{2}){2})\+\d{2}:\d{2}')
        #2021-07-12 08:26:04+00:00 -> 2021-07-12 08:26:04
        xl_datetime = p.sub('',xl_datetime)
        py_datetime = datetime.strptime(xl_datetime,'%Y-%m-%d %H:%M:%S')
        return py_datetime

Answer 8

AlexLordThorsen answer was very close. You have to match every character in the string with the correct directive. AlexLordThorsen code tried to match 'Space' string with timezone directive %z which is wrong. Below is image with better explanation

Below corrected code will work

>>> datetime.datetime.strptime("2016-04-01 17:29:25+00:00", '%Y-%m-%d %H:%M:%S%z')
datetime.datetime(2016, 4, 1, 17, 29, 25, tzinfo=datetime.timezone.utc)