Recursively replace string in directory of excel files

Question

I'm trying to replace every occurrence of a given string with another string in a directory of excel files.

for (root, dirs, files) in os.walk(DIRECTORY):
    for file in files:
        if file.endswith(".xlsx"):
            path = os.path.join(root, file)
            print("Opening: " + path)
            wb = openpyxl.load_workbook(path)
            for ws in wb.worksheets:
                for row in ws.iter_rows():
                    for cell in row:
                        print(cell.value)
                        if cell.value == target:
                            print("TARGET STRING FOUND")
                            cell.value = replace
                wb.save(wb)

I get AttributeError: 'list' object has no attribute 'endswith' when I run the script.

Thanks for any help

@luis.parravicini Sorry I forgot to add my error. I updated my question. — GTA.sprx
– GTA.sprx, Commented Nov 6, 2019 at 23:25

user5386938 · Accepted Answer · 2019-11-07 01:43:04Z

2

os.walk does not return a sequence of files. It yields (root, dirs, files).

for (root, dirs, files) in os.walk(directory):
    for name in files:
        if name.endswith(".xlsx"):
            path = os.path.join(root, name)
            # the rest of your code here

answered Nov 7, 2019 at 1:43

user5386938

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

Comments

Rajith Thennakoon · Accepted Answer · 2019-11-07 00:49:12Z

0

Try this

basepath="directory_path"
files = list(filter(lambda x: '.xlsx' in x, os.listdir(basepath)))
for file in files:
    wb = openpyxl.load_workbook(f"{basepath}/file")
    for sheet in wb.worksheets:
        for cell in sheet.iter_rows('C{}:C{}'.format(sheet.min_row,sheet.max_row)):
            print(cell + "\n")
            if cell.value == target:
                cell.value = replace

answered Nov 7, 2019 at 0:49

Rajith Thennakoon

4,1402 gold badges16 silver badges25 bronze badges

Collectives™ on Stack Overflow

Recursively replace string in directory of excel files

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related