How to search and replace string in excel (.xls) file using Python?

Question

i am trying to use search and replace string in excel file (xls), actually - i need to find string and add before prefix * . For example

But i am getting a error:

LVdemact = {'two': two_replaced, 'six': six_replaced, 'five': five_replaced}
NameError: name 'two_replaced' is not defined

Code:

from xlrd import open_workbook
from xlutils.copy import copy
import xlsxwriter

rb = open_workbook("template.xlsx")

# Create an new Excel file and add a worksheet.
wb = xlsxwriter.Workbook('Updated_Workbook1.xlsx')
ws = wb.add_worksheet()

s_orig = rb.sheet_by_index(0)

LVdemact = {'two': two_replaced, 'six': six_replaced, 'five': five_replaced,}

for row in range(s_orig.nrows):
    for col in range(s_orig.ncols):
        if s_orig.cell(row,col).value in LVdemact:
            # s.write(row, col, LVdemact[item])
            ws.write(row, col, LVdemact[s_orig.cell(row,col).value])
        else:
            ws.write(row, col, s_orig.cell(row,col).value)
wb.close()

You need to at least add something like two_replaced = "two_replaced" before you define LVdemact. — user3657941
– user3657941, Commented Aug 8, 2020 at 6:09

Tabaene Haque · Accepted Answer · 2020-08-08 06:25:13Z

1

The issue is in the line - LVdemact = {'two': two_replaced, 'six': six_replaced, 'five': five_replaced,}

variables two_replaced, six_replaced and five_replaced are not defined. Do you want this to be a string ? then define this in this way -

LVdemact = {'two': 'two_replaced', 'six': 'six_replaced', 'five': 'five_replaced'}

answered Aug 8, 2020 at 6:25

Tabaene Haque

5742 silver badges10 bronze badges

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

Comments

gulbaz khan · Accepted Answer · 2020-08-08 06:04:41Z

0

You can use this

df = pd.DataFrame() # Your dataframe

for column in df.select_dtypes('object'):
    df[column] = df[column].str.replace('toreplace','newword')

answered Aug 8, 2020 at 6:04

gulbaz khan

1752 silver badges9 bronze badges

2 Comments

john stamos Over a year ago

Once you replace the string in the data frame, how do you write back to your excel sheet though?

gulbaz khan Over a year ago

df.to_excel('<filename>') sometimes you need to add engine also. i mostly pre install openpyxl and add as argument.

Collectives™ on Stack Overflow

How to search and replace string in excel (.xls) file using Python?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related