Blog

I’m trying to write a scipt that can update a column based on a transaction ID. Im using Python3, Openpyxl to read the excel file

In the above image, it would be to update the highlighted cells with the same value in column K, as they have the same transaction ID in column C. Then when it gets to C12, it updates column K with a different value as the value of C has changed…and so on and so on.

So far I have:

from openpyxl import load_workbook, Workbook
import re

wb = load_workbook(filename = 'Testing.xlsx')
ws = wb['Test']

for r in range(2, ws.max_row + 1):
    column_c = ws.cell(row = r, column = 3).value
    column_h = ws.cell(row = r, column = 8).value
    column_i = ws.cell(row = r, column = 9).value
    column_j = ws.cell(row = r, column = 10).value

previous = None
while (previous == column_c):
    ws.cell(row = r, column = 11).value = column_j_formatted
    if (previous != column_c):
        continue

wb.save('Testing_processed.xlsx')

UPDATE

I have tried to replace the while loop with:

previous_col_c = ws.cell(row=r-1, column=3)
for row_num in range (2, ws.max_row + 1):
    current_col_c = ws.cell(row=r, column=3)
    current_col_j = ws.cell(row=r, column=11)
    if current_col_c == previous_col_c:
        ws.cell(row = r, column = 11).value = column_j_formatted
    previous_col_c = current_col_c

Solution

Just to illustrate how the openpyxl API makes this kind of task very easy.

txn = None
filler = None
for row in ws.iter_rows(min_row=2):
     a = row[0]
     k = row[10]
     if a.value != txn:
         txn = a.value
         filler = k.value
     if not k.value:
        k.value = filler

But really the work should be done in the source of the data, presumably a database.

 Answer

I’ve no idea why you’d use PHP at all for this. If you load everything into the DB in a single pass you can then just update the relevant rows

I’m failing to make the while loop write to column J as desired. I tried it using mysql and php and it took forever to process 10000 lines