How to import and analyze data from .xlsx file

[john_hobbyist]

Hello, I have PyQT application/code that I can import .tif files. I am trying to import and .xlsx files from the same button and do some processing. For example search the rows of the .xlsx file and find specific sentences, such as timestamps. Any ideas what libraries to use?

[JonB]

@john_hobbyist
From Python you have a choice of libraries for reading/writing Excel/.xlsx files. Just Google. I used openpyxl, https://openpyxl.readthedocs.io/en/stable/, it was fine. I don't know whether others are better/worse.

[john_hobbyist]

Ok I have read it, it seems very nice library. The problem I have is this:

1. I have to extract the date from the timestamp which is in this format: 2016-07-11T10:40:09+00:00 How do I achieve this?
2. I have an excel file that I read from two cells (start time - end time) and I should see if the above timestamp is in the start-end range time. Any idea how I do this?

[JonB]

@john_hobbyist

1. From Qt see QDateTime QDateTime::fromString(const QString &string, Qt::DateFormat format = Qt::TextDate), and look through the possibilities for the format in enum Qt::DateFormat . If it's not one of those there is doubtless a Python parser for this format, for Python datetime. [EDIT: strptime() https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes ]

2. Once you have converted to QDateTime or datetime, do a datetime comparison to see if your date is between the other two.

[john_hobbyist]

I am searching on the websites you posted..Can somehow convert this 2016-07-11T10:40:09+00:00 to seconds from 1970? I think it is known as epoch (?)

[JonB]

@john_hobbyist
I don't think it's "epoch", I think it's "ISO 8601" format :)

https://stackoverflow.com/questions/127803/how-do-i-parse-an-iso-8601-formatted-date
https://stackoverflow.com/questions/28331512/how-to-convert-pythons-isoformat-string-back-into-datetime-object

I think in Python 3.7+ there is now datetime.fromisoformat(date_string). If not, dateutil library can still be used.

[john_hobbyist]

@JonB : I am trying what you suggest...thanks
I meant something like this:
https://www.kite.com/python/answers/how-to-convert-the-current-date-to-an-epoch-timestamp-in-python which unfortunately I haven't managed to make it work....

And this is the code:

import openpyxl 
import datetime
from datetime import date, datetime, time
import time
from datetime import datetime

path = "/home/UbuntuUser/Desktop/Times.xlsx"
wb_obj = openpyxl.load_workbook(path) 
sheet_obj = wb_obj.active 
cell_obj = sheet_obj.cell(row = 2, column = 3) 
print(cell_obj.value) 
print(datetime.fromisoformat(cell_obj.value))
print(time.mktime(datetime.fromisoformat(cell_obj.value)))

but it is not running

partial source: https://www.geeksforgeeks.org/python-reading-excel-file-using-openpyxl-module/

Update:

  File "openpyxl_code_v.2.py", line 13, in <module>
    print(time.mktime(datetime.fromisoformat(cell_obj.value)))
TypeError: Tuple or struct_time argument required

[JonB]

@john_hobbyist
datetime.fromisoformat(cell_obj.value) returns a datetime object (at least if it works from an acceptably-formatted string). time.mktime() expects a struct_time object, or a full 9-tuple. Hence the error message.

[john_hobbyist]

Ok, so how do I convert datetime to struct_time?

[JonB]

@john_hobbyist
You don't, because you don't want to.

Why do you think you want to use time.mktime() at all? datetime.fromisoformat() gives you a datetime, that's all you want. If you want --- as I think you do? --- the number of seconds since epoch in that time, you have time.time() → float, https://docs.python.org/3/library/time.html#time.time

Return the time in seconds since the epoch as a floating point number.
[This is commonly referred to as Unix time.]

Don't even know if you need this. Like I said earlier, you can do comparisons on Python datetimes without needing to turn them into a number of seconds, which is all you said you want to do.

[john_hobbyist]

This post is deleted!