Python loop through Excel rows using Openpyxl, updating cells based on a transaction ID

Question

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

So, what problems do you have? BTW. This would be best done in a database. — Charlie Clark
– Charlie Clark, Commented Sep 18, 2019 at 16:56
@Charlie Clark 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 — Vincent H Guyo
– Vincent H Guyo, Commented Sep 19, 2019 at 1:31
I think I might be onto something with the update there, I don't know if you can clearly see what im trying to implement — Vincent H Guyo
– Vincent H Guyo, Commented Sep 19, 2019 at 2:06
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. — Charlie Clark
– Charlie Clark, Commented Sep 19, 2019 at 8:30

Charlie Clark · Accepted Answer · 2019-09-19 08:38:45Z

1

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.

answered Sep 19, 2019 at 8:38

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python loop through Excel rows using Openpyxl, updating cells based on a transaction ID

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related