How can I loop through all the xls files in a folder to find the sheet names, and then replace them

Question

I am trying to loop through all the XLS files in a folder, and then replace the worksheet name by another string. This has to be done for all the files inside.

I am relatively new to programming, and here is my Python code. It runs okay (partially, when I do it for one file at a time), however, I am unable to get it to work for all the files in the folder.

from xlutils.copy import copy
from xlrd import open_workbook

# open the file
direc = input('Enter file name: ')
rb = open_workbook(direc)

wb = copy(rb)

#index of a sheet
pointSheet = rb.sheet_names()
print(pointSheet)
idx = pointSheet.index(pointSheet)

wb.get_sheet(idx).name = u'RenamedSheet1'
wb.save(direc)

Error message:

Traceback (most recent call last):
  File "./Rename.py", line 13, in <module>
    idx = pointSheet.index(pointSheet)
ValueError: ['x xxx xxxx xxxxxx'] is not in list

My bad! The above code is for testing with a single file. Here is the loop:

files = []
for dirname, dirnames, filenames in os.walk('D:\Temp\Final'):
    # print path to all subdirectories first.
    for subdirname in dirnames:
        files.append(os.path.join(dirname, subdirname))

    # print path to all filenames.
    for filename in filenames:
        files.append(os.path.join(dirname, filename))


pprint(files)

for i in range(0,len(files)):
    rb = open_workbook(files[i])
    wb = copy(rb)
    idx = rb.sheet_names().index('5 new bulk rename')
    wb.get_sheet(idx).name = u'RenamedSheet1'
    wb.save(files[i])

print('Operation succeeded!')

This code doesn't contain a loop? And I don't know how the `.index` function works, but I assume that `.sheet_names()` returns an array of names, and that you need to loop through that array to rename the seperate sheets. — Robin De Schepper, Oct 10 '19 at 10:09
I think because you're passing an array instead of a string into the `index` function is why the errors says `['x xxx xxxx xxxxxx']` instead of just `'x xxx xxxx xxxxxx'` — Robin De Schepper, Oct 10 '19 at 10:16

score 0 · Accepted Answer · answered Oct 10 '19 at 10:14

Try something like this (untested) for a single file:

from xlutils.copy import copy
from xlrd import open_workbook

# open the file
direc = input('Enter file name: ')
rb = open_workbook(direc)

wb = copy(rb)

for pointSheet in rb.sheet_names()
  print(pointSheet)
  idx = pointSheet.index(pointSheet)
  wb.get_sheet(idx).name = u'RenamedSheet1'
wb.save(direc)

And wrap that in another loop using listdir (taken from here):

import os
for file in os.listdir("/mydir"):
    if file.endswith(".xls"):
        # <do what you did for a single file>

How can I loop through all the xls files in a folder to find the sheet names, and then replace them

1 Answers1