String replacement in excel using Python

Question

I want to remove % and replace with a string, and to remove space and replace it with under score.

This is what I have done so far:

# Open Excel file from a user imput
import xlrd, xlwt
filename = raw_input("Enter Excel file name with extension (.xls) and path")
oldbook = xlrd.open_workbook(filename)
newbook = xlwt.Workbook()

# For all the sheets in the workbook
for sheetname in oldbook.sheet_names():
    oldsheet = oldbook.sheet_by_name(sheetname)
    newsheet = newbook.add_sheet(sheetname)

    # For all the rows and all the columns in an excel
    for ii in range(oldsheet.nrows):
        for jj in range(oldsheet.ncols):
            # Replace
            range.replace("%", "Perc")

# Save the file in a desired location with the desired name
savelocation = raw_input("Enter a new path and file name with extension (.xls) to save the new Excel spread sheet ")
newbook.save(savelocation)

hstay · Accepted Answer · 2014-03-28 07:45:04Z

2

One advice, read cell data into a string and then manipulate it.

Try this: (Unfortunately I cannot run it at the moment)

# Open Excel file from a user imput
import xlrd, xlwt
filename = raw_input("Enter Excel file name with extension (.xls) and path")
oldbook = xlrd.open_workbook(filename)
newbook = xlwt.Workbook()

# For all the sheets in the workbook
for sheetname in oldbook.sheet_names():
    oldsheet = oldbook.sheet_by_name(sheetname)
    newsheet = newbook.add_sheet(sheetname)

    # For all the rows and all the columns in an excel
    for ii in range(oldsheet.nrows):
        for jj in range(oldsheet.ncols):
            # Replace
            CellString=str(oldsheet.cell(ii, jj).Value)
            CellString=CellString.replace("%", "Perc")
            CellString=CellString.replace(" ", "_")
            newsheet.write(ii, jj, CellString)
# Save the file in a desired location with the desired name
savelocation = raw_input("Enter a new path and file name with extension (.xls) to save the new Excel spread sheet ")
newbook.save(savelocation)

edited Mar 28, 2014 at 7:45

answered Mar 27, 2014 at 17:21

hstay

1,4391 gold badge11 silver badges20 bronze badges

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

5 Comments

user256683 Over a year ago

i ran the code as you said and nothing happened. i dont get an error but the values whwere not replaced

user256683 Over a year ago

Yes i get the spreadsheet with all my numbers formatted as string but they are not changed.... the % are still symbols and empty spaces in the excel are not subbed with underscore.

John Y Over a year ago

The replace method does not modify strings in-place. (Actually, Python strings cannot be modified in-place.) Instead, the method returns a new string. So you need to have something to receive the return value (which could be the same name if you like).

hstay Over a year ago

True! I don't know what I was thinking yesterday. Thanks a lot, now it is corrected.

user256683 Over a year ago

i still dont get it. i added another variable to received the replace but no result. how do i put the replaced value back where they belong ?

Collectives™ on Stack Overflow

String replacement in excel using Python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related