Deleting rows based on cell value in Excel - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Deleting rows based on cell value in Excel (/thread-38414.html) Pages:
1
2
|
Deleting rows based on cell value in Excel - azizrasul - Oct-09-2022 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? for row in ws.iter_rows(): if not any([cell.value for cell in row[1:]]): ws.delete_rows(row[0].row) RE: Deleting rows based on cell value in Excel - Pedroski55 - Oct-10-2022 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: This deletes rows containing a name in which the letters X or x appeardef 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) RE: Deleting rows based on cell value in Excel - azizrasul - Oct-10-2022 Fantastic, many thanks. RE: Deleting rows based on cell value in Excel - Pedroski55 - Oct-11-2022 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. 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) RE: Deleting rows based on cell value in Excel - azizrasul - Oct-11-2022 I hadn't got a chance until now to run the code. I get an error: - if 'X' in row[1].value or 'x' in row[1].value: TypeError: argument of type 'int' is not iterable RE: Deleting rows based on cell value in Excel - Pedroski55 - Oct-12-2022 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 I can't even get Pycharm to work, or I'd try it there!I'm sure you'll find the problem! RE: Deleting rows based on cell value in Excel - azizrasul - Oct-12-2022 OK thanks I will need to play with it and see why it's not working. RE: Deleting rows based on cell value in Excel - azizrasul - Oct-13-2022 I changed (based on post #2) if 'X' in row[1].value or 'x' in row[1].value:to 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) elif row[1].value == 'x' or row[1].value=='x':I get the error AttributeError: 'str' object has no attribute 'value'What am I doing wrong? RE: Deleting rows based on cell value in Excel - Pedroski55 - Oct-13-2022 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 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, RE: Deleting rows based on cell value in Excel - azizrasul - Oct-13-2022 It may be that I am using PyCharm environment that is causing the issue. Sigh. |