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?

Zielinski

can you show us what the string looks like? str(excel_ws.Cells(2, 4).Value) – Antiquate

Have you considered using openpyxl? It doesn't require a version of Excel to be installed or automated and handles converting cells with dates to the native python datetime for you... – Phosphatase

Sure. I just threw in a print statement, and this is what it looks like: 2016-04-01 17:29:25+00:00 Now I feel silly for not doing that earlier. If it's always going to add a '+00:00' on the end, clearly I can use a simple splice and get what I need. However, I still would like to know if there's a better way to handle this. – Zielinski

I hadn't, Jon. The advantage of not needing Excel to be installed doesn't do much for me since I have Excel installed, use it every day, and so on. If PyWin32 continues to irritate me with lack of documentation, however, I may very well check it out soon. (I was using xlrd and xlwt before this, but xlwt can't handle .xlsx, and I figured: why use xlrd and PyWin32 when I could just use PyWin32? So that's how I got where I'm at.) – Zielinski

Don't have a windows box but this is the closest thing I could find. I know it uses pywintypes.Time instead of .datetime but worth a shot. timgolden.me.uk/python/win32_how_do_i/… – Mauromaurois

Answer 1

So the problem is the +00:00 timezone offset. Looking into this there's not an out of the box solution for Python

datetime.datetime.strptime("2016-04-01 17:29:25+00:00", '%Y-%m-%d %H:%M:%S %z')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/_strptime.py", line 324, in _strptime
    (bad_directive, format))
ValueError: 'z' is a bad directive in format '%Y-%m-%d %H:%M:%S %z'

One band-aid solution is to strip the timezone but that feels pretty gross.

datetime.datetime.strptime("2016-04-01 17:29:25+00:00".rstrip("+00:00"), '%Y-%m-%d %H:%M:%S')
datetime.datetime(2016, 4, 1, 17, 29, 25)

Looking around it looks like (if you can use a third party library) dateutil solves this issue and is nicer to use then datetime.strptime.

On Commandline

pip install python-dateutil

code

>>> import dateutil.parser                                                      
>>> dateutil.parser.parse("2016-04-01 17:29:25+00:00")
datetime.datetime(2016, 4, 1, 17, 29, 25, tzinfo=tzutc())

Antiquate

Thanks, Alex. For tonight, I just stripped out the timezone. Agreed, it feels gross. For future (I'm going to continue to develop this), I'm going to look into dateutil. I hate date/time issues in general, so that might be helpful. Thanks for your research and solution. – Zielinski

instead of "2016-04-01 17:29:25+00:00".rstrip("+00:00"), we should use "2016-04-01 17:29:25+00:00".rsplit("+00:00")[0]. Using rstrip removes all combinations of matching characters, which can have some unintended consequences, docs.python.org/library/stdtypes.html – Cabal

Answer 2

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

Monarchal

Answer 3

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)

Dracula

Answer 4

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)

Mimas

Your answer seemed VERY pythonic to me, but it did not convert to datetime.datetime object in my case. – Palladin

@ThomIves Please share a snippet and python version you are using. I'm curious – Mimas

Sorry! I think I solved the issue up or down-stream in JavaScript or somewhere else and can't remember now. I should've have given an example at the time. – Palladin

Answer 5

You can also just use datetime.datetime.fromisoformat(), which is probably the easiest way.

import datetime 

datetime.datetime.fromisoformat("2016-04-01 17:29:25+00:00")

Thrifty

This works pretty well but seems to clear the tzinfo in my case. – Tobacconist

Answer 6

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')

Nimocks

Answer 7

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'
>>> 

Trusteeship

Answer 8

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)

Chatterjee

Answer 9

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

Stone

Answer 10

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)

Gilreath