1

I am trying to run through an Excel sheet and copy all rows where column "B" starts with value "A-P1" or "A-S1" and copy the row to two separate Excel files (one file for P1 and one for S1 values).

Currently I have sort of achieved this with the following code:

for row in ws.iter_rows(values_only=True):
    for value in row:
        if str(value).startswith("A-P1"):
            ws2.append(row)
        elif str(value).startswith("A-S1"):
            ws3.append(row)

ws being to original excel file and ws2 and 3 being the new one. This works, and I am able to create two new excel files with the rows added as expected. BUT; I also know there are rows where column B does not start with A-P1 or A-S1, but a random string. I also need to separate these rows, but I am not able to do so. Adding

else:
ws.append(row)

appends all rows in the original sheet several times. I guess this is because it loops through all cells in the row, and each row have 13 columns.

So in short, what I would like is "if value in row(Column"B") starts with X then copy entire row". How is this possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
suprimos
  • 33
  • 6
  • ***"if value in row(Column"B") starts with X then copy entire row".***: `row[1]` is **Column B**. Change to `if row[1].value.startswith(...`. You don't need the `for value in row:` loop. – stovfl Mar 07 '20 at 12:51
  • This works after removing `values_only=True` from `iter_rows()`. Also works when removing `for value` loop. Thanks! Also able to catch rows where Column B is not as expected. – suprimos Mar 07 '20 at 16:31
  • ***"works after removing `values_only=True`"***: My bad, should read: ***"if row[1].startswith(...:"*** – stovfl Mar 07 '20 at 16:48

0 Answers0