Deleting rows based on cell value in Excel

[azizrasul]

The following snippet of code deletes empty rows. How can I change the code such that a row is deleted if a value of 'x' appears in any row in column C?

1 2 3

for row in ws.iter_rows():     if not any([cell.value for cell in row[1:]]):         ws.delete_rows(row[0].row)

[Pedroski55]

This Excel file contains some names containing X or x in column 2 and some None values in column 2 or 3

Before the Excel looks like this:

Output:
>>> for row in sF[sFsheet].values:
    print(row)

    
('id', 'name', 'Age')
(1, 'Peter', 1)
(2, 'Xavier', 2)
(3, 'George', 3)
(4, None, None)
(5, 'Peggy-Sue', 4)
(6, 'Xander', 5)
(7, 'Legolas', 6)
(8, 'Happy', 7)
(9, None, None)
(10, 'Fatty', 8)
(11, 'Sox', 9)
(12, 'Justin', 10)

This deletes rows containing a name in which the letters X or x appear

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

def myApp():     path2XL = '/home/pedro/myPython/openpyxl/xlsx_files/'     file = 'some_X_or_x_in_column_2.xlsx'       sF = openpyxl.load_workbook(path2XL + file)     sFsheets = sF.sheetnames     sFsheet = sFsheets[0]     sFmaxRow = sF[sFsheet].max_row       # open a new XL     name = file.split('.')     savename = name[0] + '_modified.xlsx'     newXL = openpyxl.Workbook()     sheets = newXL.sheetnames     sheet = sheets[0]       # before     for row in sF[sFsheet].values:         print(row)       # you can choose the rows and columns you want to iterate     # for row in sF[sFsheet].iter_rows(min_row=1, min_col=1, max_row=6, max_col=2):     # look through column 2     # if a name contains X or x delete that row     # row[1] is the value in column 2 of the XL     # if there is an X or x in a header name, the headers row will be gone! Fix that!     for row in sF[sFsheet].iter_rows():         if row[1].value == None:             continue         if 'X' in row[1].value or 'x' in row[1].value:             sF[sFsheet].delete_rows(row[0].row)               # after     for row in sF[sFsheet].values:         print(row)       sF.save(path2XL + savename)

[azizrasul]

Fantastic, many thanks.

[Pedroski55]

I find, in Python, there is almost always more than 1 way to do things.

This creates a new modified file, where names with X or x or None values are not copied.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

def myApp():     import openpyxl       path2XL = '/home/pedro/myPython/openpyxl/xlsx_files/'     file = 'some_X_or_x_in_column_2.xlsx'       sF = openpyxl.load_workbook(path2XL + file)     sFsheets = sF.sheetnames     sFsheet = sFsheets[0]     sFmaxRow = sF[sFsheet].max_row       # open a new XL     name = file.split('.')     savename = name[0] + '_modified.xlsx'     newXL = openpyxl.Workbook()     sheets = newXL.sheetnames     sheet = sheets[0]       # copy straight from sF, except if you find X or x in column 2     count = 1     for row in sF[sFsheet].values:         print(row)         if count == 1:             print('Inserting the headers ... ')             for i in range(len(row)):                 newXL[sheet].cell(row=count, column=i+1).value=row[i]             count +=1             continue         if row[1] == None:             print('Found a None value ... ')             continue         elif 'X' in row[1] or 'x' in row[1]:             print('Found an X or x in column 2 ... ')             continue         else:             print('row is', row)             for i in range(len(row)):                 print('row[i] is', row[i])                 newXL[sheet].cell(row=count, column=i+1).value=row[i]             count +=1       newXL.save(path2XL + savename)

[azizrasul]

I hadn't got a chance until now to run the code. I get an error: -

1 2	if 'X' in row[1].value or 'x' in row[1].value: TypeError: argument of type 'int' is not iterable

[Pedroski55]

Funny, it works for me.

In post #2 I should have saved sF, not newXL, or that will be empty. Actually, you don't need newXL at all in post #2

Below is the output in Idle for post #4

Output:
>>> myApp()
('id', 'name', 'Age')
Inserting the headers ... 
(1, 'Peter', 1)
row is (1, 'Peter', 1)
row[i] is 1
row[i] is Peter
row[i] is 1
(2, 'Xavier', 2)
Found an X or x in column 2 ... 
(3, 'George', 3)
row is (3, 'George', 3)
row[i] is 3
row[i] is George
row[i] is 3
(4, None, None)
Found a None value ... 
(5, 'Peggy-Sue', 4)
row is (5, 'Peggy-Sue', 4)
row[i] is 5
row[i] is Peggy-Sue
row[i] is 4
(6, 'Xander', 5)
Found an X or x in column 2 ... 
(7, 'Legolas', 6)
row is (7, 'Legolas', 6)
row[i] is 7
row[i] is Legolas
row[i] is 6
(8, 'Happy', 7)
row is (8, 'Happy', 7)
row[i] is 8
row[i] is Happy
row[i] is 7
(9, None, None)
Found a None value ... 
(10, 'Fatty', 8)
row is (10, 'Fatty', 8)
row[i] is 10
row[i] is Fatty
row[i] is 8
(11, 'Sox', 9)
Found an X or x in column 2 ... 
(12, 'Justin', 10)
row is (12, 'Justin', 10)
row[i] is 12
row[i] is Justin
row[i] is 10

I can't even get Pycharm to work, or I'd try it there!

I'm sure you'll find the problem!

[azizrasul]

OK thanks I will need to play with it and see why it's not working.

[azizrasul]

I changed (based on post #2)

1	if 'X' in row[1].value or 'x' in row[1].value:

to

1	if row[1].value == 'X' or row[1].value=='x':

and it works now.

Referencing post #4, I get an error on line (which I have changed, in line with I have said above)

1	elif row[1].value == 'x' or row[1].value=='x':

I get the error

1	AttributeError: 'str' object has no attribute 'value'

What am I doing wrong?

[Pedroski55]

I don't know, sorry!

I copied and pasted Post #4 straight into Idle and it worked exactly as I hoped!

I didn't change anything.

Change it back and try again!

This is post #2 changed, because I didn't need a new Excel for post #2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

def myApp():     import openpyxl     path2XL = '/home/pedro/myPython/openpyxl/xlsx_files/'     file = 'some_X_or_x_in_column_2.xlsx'     name = file.split('.')     savename = name[0] + '_modified.xlsx'        sF = openpyxl.load_workbook(path2XL + file)     sFsheets = sF.sheetnames     sFsheet = sFsheets[0]     sFmaxRow = sF[sFsheet].max_row        # before     for row in sF[sFsheet].values:         print(row)        # you can choose the rows and columns you want to iterate     # for row in sF[sFsheet].iter_rows(min_row=1, min_col=1, max_row=6, max_col=2):     # look through column 2     # if a name contains X or x delete that row     # row[1] is the value in column 2 of the XL     # if there is an X or x in a header name, the headers row will be gone! Fix that!     for row in sF[sFsheet].iter_rows():         if row[1].value == None:             continue         if 'X' in row[1].value or 'x' in row[1].value:             sF[sFsheet].delete_rows(row[0].row)                # after     for row in sF[sFsheet].values:         print(row)        sF.save(path2XL + savename)

From this, you can see how .delete_rows works: it adds and empty row, then deletes, otherwise you would get and index error,

[azizrasul]

It may be that I am using PyCharm environment that is causing the issue. Sigh.