Find and replace strings in Excel (.xlsx) using Python

Question

I am trying to replace a bunch of strings in an .xlsx sheet (~70k rows, 38 columns). I have a list of the strings to be searched and replaced in a file, formatted as below:-

bird produk - bird product
pig - pork
ayam - chicken
...
kuda - horse

The word to be searched is on the left, and the replacement is on the right (find 'bird produk', replace with 'bird product'. My .xlsx sheet looks something like this:-

name     type of animal     ID
ali      pig                3483
abu      kuda               3940
ahmad    bird produk        0399
...
ahchong  pig                2311

I am looking for the fastest solution for this, since I have around 200 words in the list to be searched, and the .xlsx file is quite large. I need to use Python for this, but I am open to any other faster solutions.

Edit:- added sheet example

Edit2:- tried some python codes to read the cells, took quite a long time to read. Any pointers?

from xlrd import open_workbook
wb = open_workbook('test.xlsx')

for s in wb.sheets():
    print ('Sheet:',s.name)
    for row in range(s.nrows):
        values = []
        for col in range(s.ncols):
            print(s.cell(row,col).value)

Thank you!

Edit3:- I finally figured it out. Both VBA module and Python codes work. I resorted to .csv instead to make things easier. Thank you! Here is my version of the Python code:-

import csv

###### our dictionary with our key:values. ######
reps = {
    'JUALAN (PRODUK SHJ)' : 'SALE( PRODUCT)',
    'PAMERAN' : 'EXHIBITION',
    'PEMBIAKAN' : 'BREEDING',
    'UNGGAS' : 'POULTRY'}


def replace_all(text, dic):
    for i, j in reps.items():
        text = text.replace(i, j)
    return text

with open('test.csv','r') as f:
    text=f.read()
    text=replace_all(text,reps)

with open('file2.csv','w') as w:
    w.write(text)

Is this a question about accessing things in a spreadsheet, or how to manipulate what you find there? — Scott Hunter
– Scott Hunter, Commented Oct 6, 2014 at 18:00
@Scott Hunter how to manipulate what I have in my .xlsx sheet. I can read the file, but I need to do a string match based on the list that I have in another separate file, and replace the strings in the .xlsx with new ones. — antsemot
– antsemot, Commented Oct 6, 2014 at 18:08

Mr. Mascaro · Accepted Answer · 2014-10-06 18:33:31Z

1

I would copy the contents of your text file into a new worksheet in the excel file and name that sheet "Lookup." Then use text to columns to get the data in the first two columns of this new sheet starting in the first row.

Paste the following code into a module in Excel and run it:

Sub Replacer()
    Dim w1 As Worksheet
    Dim w2 As Worksheet

    'The sheet with the words from the text file:
    Set w1 = ThisWorkbook.Sheets("Lookup")
    'The sheet with all of the data:
    Set w2 = ThisWorkbook.Sheets("Data")

    For i = 1 To w1.Range("A1").CurrentRegion.Rows.Count
        w2.Cells.Replace What:=w1.Cells(i, 1), Replacement:=w1.Cells(i, 2), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Next i

End Sub

answered Oct 6, 2014 at 18:33

Mr. Mascaro

2,7431 gold badge13 silver badges18 bronze badges

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

7 Comments

antsemot Over a year ago

i've copied the contents into a new sheet and used the text to columns to separate the data. Am now running the codes.

antsemot Over a year ago

I just finished running the codes, it seems that some of the strings are not replaced correctly. and there are some which are not even replaced at all. Please advise.

Mr. Mascaro Over a year ago

If it's a problem with only some of them, I would guess that the text to columns was not done properly and there are extra spaces around the text in the lookup.

antsemot Over a year ago

okay thank you, I will look that up. By the way, if I want to search the whole cell (not just part of a string), I should change LookAt:=xlPart to LookAt:=xlWhole, correct?

Mr. Mascaro Over a year ago

Yes, but that will make the problem with spaces worse. To get rid of the spaces you can use the TRIM(...) function and then copy and paste values. That should be the easiest way.

|

akshaynagpal · Accepted Answer · 2014-10-06 18:01:55Z

0

Make 2 arrays A[bird produk, pig, ayam, kuda] //words to be changed B[bird product, pork, chicken, horse] //result after changing the word

Now check each row of your excel and compare it with every element of A. If i matches then replace it with corresponding element of B.

for example // not actual code something like pseudocode

for (i=1 to no. of rows.)
{
for(j=1 to 200)
{
if(contents of row[i] == A[j])
then contents of row[i]=B[j] ;
break;
}
}

To make it fast you have to stop the current iteration as soon as the word is replaced and check the next row.

answered Oct 6, 2014 at 18:01

akshaynagpal

3,17734 silver badges33 bronze badges

Comments

Scott Hunter · Accepted Answer · 2014-10-06 18:19:52Z

0

Similar idea to @coder_A 's, but use a dictionary to do the "translation" for you, where the keys are the original words and the value for each key is what it gets translated to.

answered Oct 6, 2014 at 18:19

Scott Hunter

50k12 gold badges64 silver badges107 bronze badges

Comments

laike9m · Accepted Answer · 2014-10-11 09:19:13Z

0

For reading and writing xls with Python, use xlrd and xlwt, see http://www.python-excel.org/

A simple xlrd example:

from xlrd import open_workbook
wb = open_workbook('simple.xls')

for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        values = []
        for col in range(s.ncols):
            print(s.cell(row,col).value)

and for replacing target text, use a dict

replace = {
    'bird produk': 'bird product',
    'pig': 'pork',
    'ayam': 'chicken'
    ...
    'kuda': 'horse'
}

Dict will give you O(1)(most of the time, if keys don't collide) time complexity when checking membership using 'text' in replace. there's no way to get better performance than that.

Since I don't know what your bunch of strings look like, this answer may be inaccurate or incomplete.

edited Oct 11, 2014 at 9:19

answered Oct 6, 2014 at 18:23

laike9m

19.6k23 gold badges117 silver badges154 bronze badges

4 Comments

antsemot Over a year ago

I've added an example of the sheet in my question, @laike9m

laike9m Over a year ago

@antsemot I see. Then you just need to iterate over all cell values as my first code snippet shows.

antsemot Over a year ago

I tested the code with 5 rows (38 columns), it took so long (> 5 minutes). Is it normal?

antsemot Over a year ago

please refer to my post (edited). I've just tried to read the cells but it took a while so I'm guessing it would take longer to read, find and replace the cells.

Collectives™ on Stack Overflow

Find and replace strings in Excel (.xlsx) using Python

4 Answers 4

7 Comments

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related