CODEWITHSUNDEEP
pythonexcelopenpyxl
Anas
Anas

Reputation: 39

Search specific text(text pattern) in excel and copy all resulting rows to another sheet in same workbook using openpyxl

I have an excel file with multiple sheets, 3rd column(contains around 500 rows) of sheet3 contains various names. I want to search column 3 for specific text and if it matches then copy the whole row along with the header row to new sheet within same excel.

Issue with "name column" is that most of the text refer to same item but naming convention is different, so: अपर तहसीलदार, नायाब तहसीलदार, नायब तहसीलदार,
अतिरिक्त तहसीलदार

refers to same item but written differently, so for that I have to search for all variants.

I have no prior Python or openpyxl background so what I've got so far is:

import openpyxl
wb = openpyxl.load_workbook(r'C:/Users/Anas/Downloads/rcmspy.xlsx')

#active worksheet data
ws = wb.active

def wordfinder(searchString):
    for i in range(1, ws.max_row + 1):
        for j in range(1, ws.max_column + 1):
            if searchString == ws.cell(i,j).value:
                print("found")
                print(ws.cell(i,j))


wordfinder("अपर तहसीलदार")

It is not showing any error but don't print anything either. The excel sheet looks something like this: enter image description here

Upvotes: 1

Views: 852

Answers (1)

Eric Jensen
Eric Jensen

Reputation: 303

I'm not certain, but I would suggest something along the lines of:

variants = {'alpha','alfa','elfa'}
data = []
rowCount = 0
for row in ws.values:
    //each row is an array of cells
    if rowCount == 0:
        //header row
        data.append(row)
    elif row[2] in variants:
        data.append(row)
    rowCount += 1

wsNew = wb.create_sheet('Variations')
for line in data:
    wsNew.append(line)

wb.save('newWorkbook.xlsx')

Upvotes: 1

Related Questions