Use python to pull variable sized range of cells between 2 known fields from Excel file

user8oLc 0
Viewed 43

I want to pull data from an Excel file that matches certain fields. See sample worksheet below: enter image description here

The fields I need are the dispatch and arrival times but I also need the values in between the two cells in the first column marked "Incident Notes:" and "Event log" (highlighted in bold).
For instance, in the first event, the cells I need are:

  • 08/02/2022 4:02:29
  • 08/02/2022 4:04:22
  • Pizza with cheese
  • Did not tip

So there are 2 cells in that range between "Incident Notes" and "Event log" in the first call. In the second call however, there is only a single cell: "Italian sub". In the third call there are 3 cells: "Hot wings" "Rude" "No street parking". This is the code I have so far but I am stuck on how to pull a range of cells that vary in number between 2 known fields.

wb = openpyxl.load_workbook('TestSpreadsheet.xlsx')
sheet = wb['Sheet1']
for row in range(1, sheet.max_row):
    myrow = sheet[row]
    callstatus = sheet['C' + str(row)].value
    if callstatus == 'Dispatch:':
        calltime =sheet['C' + str(row+1)].value
        print(f" Status: {callstatus} at {calltime}")
#   TODO: figure out how to pull range of cells between
#   Incident Notes: and Event log: cells

I know I want some kind of while loop that does something like

while after 'incident notes:' cell and before 'event log' cell print cells in between

but am stuck on this.

openpyxl
1 Answers

You can search for the known word(s) that delineate each section then use offsets to find the data from that position since it seems this is consistent in your example data.
Offset allows you to select another cell at row and column from the current cell.
Below is an example code for your sample; 

import openpyxl


def fill_list(row_cell):
    # Get the cell values by offset positions
    data_list = []
    corrent_row = 0
    search_term2 = 'incident notes:'
    search_term3 = 'event log:'

    data_list.append(row_cell.offset(column=2, row=2).value)
    data_list.append(row_cell.offset(column=4, row=2).value)

    while row_cell.offset(row=corrent_row).value.lower() != search_term2:
        corrent_row += 1
    corrent_row += 1

    while row_cell.offset(row=corrent_row).value.lower() != search_term3:
        data_list.append(row_cell.offset(row=corrent_row).value)
        corrent_row += 1

    return data_list, row_cell.row + corrent_row


filename = 'foo.xlsx'
search_term1 = 'event no:'

wb = openpyxl.load_workbook(filename)
ws = wb['Sheet1']

row_next = 0
for row_cell in (ws['A']):
    cur_cell_val = row_cell.value
    
    # Jump down to the next row after the last processed data
    if row_cell.row < row_next:
        continue

    if cur_cell_val.lower() == search_term1:
        event_list, row_next = fill_list(row_cell)
        print(event_list)

['08/02/2022 4:02:29', '08/02/2022 4:04:22', 'Pizza with cheese', 'Did not tip']
['08/02/2022 5:01:12', '08/02/2022 5:14:18', 'Italian sub']
['08/02/2022 6:12:37', '08/02/2022 6:14:50', 'Hotwings', 'Rude', 'No street parking']


---------Additional answers-------
For the offset method both column and row are optional. As you can see from the code extract from the module below both are set to 0 by default 
def offset(self, row=0, column=0):
    """Returns a cell location relative to this cell.

    :param row: number of rows to offset
    :type row: int

Not setting either will mean of course that you are referencing the same cell as your current. However referencing a cell in the same plane as your current cell there is no need to set that attribute to 0.

The dates are down to the Excel formatting used on the cell. I don't know what formatting you have on any of the cells so for the test output I set the date cells as text. Your sheet appears is using a custom format for these date/time cells. If that format was like 'dd-mmm-yy hh:mm:ss' it would display as shown in Excel however its probably using forward slashes so python is interpreting as datetime.datetime(2022, 8, 2, 4, 2, 29). If you can/do not want to change the cell formatting then just convert in python. The exact conversion will probably depend on what you want to do with the dates. If its just for display you can do the following. The default will display with hyphens the second line will change the hyphens to forward slashes.

import datetime
print(datetime.datetime(2022, 2, 8, 5, 1, 12))
print(str(datetime.datetime(2022, 2, 8, 5, 1, 12)).replace('-', '/'))

Output
2022-02-08 05:01:12
2022/02/08 05:01:12

moken0
Related