Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. How to import and analyze data from .xlsx file
Forum Updated to NodeBB v4.3 + New Features

How to import and analyze data from .xlsx file

Scheduled Pinned Locked Moved Unsolved General and Desktop
11 Posts 2 Posters 2.2k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • J Offline
    J Offline
    john_hobbyist
    wrote on last edited by
    #1

    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?

    JonBJ 1 Reply Last reply
    0
    • J 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?

      JonBJ Online
      JonBJ Online
      JonB
      wrote on last edited by JonB
      #2

      @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.

      1 Reply Last reply
      4
      • J Offline
        J Offline
        john_hobbyist
        wrote on last edited by john_hobbyist
        #3

        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?
        JonBJ 1 Reply Last reply
        0
        • J 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?
          JonBJ Online
          JonBJ Online
          JonB
          wrote on last edited by JonB
          #4

          @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.

          1 Reply Last reply
          3
          • J Offline
            J Offline
            john_hobbyist
            wrote on last edited by john_hobbyist
            #5

            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 (?)

            JonBJ 1 Reply Last reply
            0
            • J 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 (?)

              JonBJ Online
              JonBJ Online
              JonB
              wrote on last edited by
              #6

              @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.

              1 Reply Last reply
              2
              • J Offline
                J Offline
                john_hobbyist
                wrote on last edited by john_hobbyist
                #7

                @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
                
                
                JonBJ 1 Reply Last reply
                0
                • J 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
                  
                  
                  JonBJ Online
                  JonBJ Online
                  JonB
                  wrote on last edited by
                  #8

                  @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.

                  1 Reply Last reply
                  3
                  • J Offline
                    J Offline
                    john_hobbyist
                    wrote on last edited by
                    #9

                    Ok, so how do I convert datetime to struct_time?

                    JonBJ 1 Reply Last reply
                    0
                    • J john_hobbyist

                      Ok, so how do I convert datetime to struct_time?

                      JonBJ Online
                      JonBJ Online
                      JonB
                      wrote on last edited by JonB
                      #10

                      @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.

                      J 1 Reply Last reply
                      1
                      • JonBJ 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.

                        J Offline
                        J Offline
                        john_hobbyist
                        wrote on last edited by john_hobbyist
                        #11
                        This post is deleted!
                        1 Reply Last reply
                        0

                        • Login

                        • Login or register to search.
                        • First post
                          Last post
                        0
                        • Categories
                        • Recent
                        • Tags
                        • Popular
                        • Users
                        • Groups
                        • Search
                        • Get Qt Extensions
                        • Unsolved