Unsolved How to import and analyze data from .xlsx file
-
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?
-
@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. -
Ok I have read it, it seems very nice library. The problem I have is this:
- 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?
- 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?
-
-
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 ] -
Once you have converted to
QDateTime
ordatetime
, do a datetime comparison to see if your date is between the other two.
-
-
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 (?)
-
@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-objectI think in Python 3.7+ there is now
datetime.fromisoformat(date_string)
. If not,dateutil
library can still be used. -
@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
-
@john_hobbyist
datetime.fromisoformat(cell_obj.value)
returns adatetime
object (at least if it works from an acceptably-formatted string).time.mktime()
expects astruct_time
object, or a full 9-tuple. Hence the error message. -
Ok, so how do I convert datetime to struct_time?
-
@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 adatetime
, that's all you want. If you want --- as I think you do? --- the number of seconds since epoch in that time, you havetime.time() → float
, https://docs.python.org/3/library/time.html#time.timeReturn 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
datetime
s without needing to turn them into a number of seconds, which is all you said you want to do. -
This post is deleted!