3
\$\begingroup\$

I have hundreds of Excel-sheets looking something like this:

enter image description here

I can't use the actual sheet, and not the exact document numbers, since they are confidential.


The name of the files are something like

2301 Item 1.xlsx
2014 Item 2.xlsx
5321 Item 3.xlsx
3212 Item 200.xlsx

I want to change the names of these files, based on the document name that's given in cell C5 in each Excel-sheet. Note: the cells C5 - E5 are merged.


I've written the following Python 3 script:

import os
import xlrd

def rename_excel_files():

    path = r"C:\Users\Stewie\Documents\excel_folder\\"

    for filename in os.listdir(path):

        excel_file = xlrd.open_workbook(path + filename)
        work_sheet = excel_file.sheet_by_index(0)
        new_filename = work_sheet.cell_value(4,2)
        os.rename(path + '\\' + filename, path + new_filename + '.xlsx')

rename_excel_files()

It works perfectly fine... But I guess it's not perfect..?

Any tips?


Note, this finishes almost instantly, so I don't really want to improve the execution time if that results in code that's harder to read, write and understand.

Also note: It's always cell C5. I don't need the cell-number to be dynamic. This is a fixed template that's used on literally thousands of files. I will only use it on the files on that template.

\$\endgroup\$

3 Answers 3

3
\$\begingroup\$

The good practice is to finish your code with

if __name__ == '__main__':
     sys.exit(main())

which has the advantage of returning an error code to the system (for example for batch processing of related programs).

It means 3 things for changing your code:

  1. import sys

  2. moving your command rename_excel_files(r"C:\Users\Stewie\Documents\excel_folder") to the new created main() function.

  3. adding an return statement to the main() function with value 0 in the case of the success (and optionally something else for the failure):

So the result will be something as

import os
import sys
import xlrd
    ...
    ...
    ...

def main():
    rename_excel_files(r"C:\Users\Stewie\Documents\excel_folder")
    return 0

if __name__ == '__main__':
    sys.exit(main())

(See "3.3.1 Use sys.exit in your script to return proper error codes" in the book Writing Idiomatic Python by Jeff Knupp, 2013 - or directly from our BDFL - in All Things Pythonic - Python main() functions by Guido van van Rossum May 15, 2003)

\$\endgroup\$
2
  • \$\begingroup\$ Could you link to the source where it's specified that what you suggested is a good practice? IMO it kinda' looks verbose and unnecessary and I've never read anywhere about it :) \$\endgroup\$ Aug 25, 2017 at 13:40
  • \$\begingroup\$ @MrGrj thanks for your comment - I added some links at the end of my answer. \$\endgroup\$
    – MarianD
    Aug 25, 2017 at 13:52
2
\$\begingroup\$

Instead of hardcoding the path to the files to be renamed, I would use a parameter. This will let you expand the script to allow to pass that path on the command-line or via a GUI or whatever later.

I would also use os.path.join instead of manually concatenating the path, the intent feels cleaner.

Lastly, an if __name__ == '__main__': guard would allow you to import that file without executing any logic:

import os
import xlrd


def rename_excel_files(path):
    """TODO: add a docstring here"""

    for filename in os.listdir(path):
        filepath = os.path.join(path, filename)
        excel_file = xlrd.open_workbook(filepath)
        work_sheet = excel_file.sheet_by_index(0)
        new_filename = work_sheet.cell_value(4,2)
        os.rename(filepath, os.path.join(path, new_filename + '.xlsx'))


if __name__ == '__main__':
    rename_excel_files(r'C:\Users\Stewie\Documents\excel_folder')
\$\endgroup\$
1
\$\begingroup\$
  • os.rename could raise OSError if the destination directory already exists on Windows. So, perhaps you want to handle that. Or you could use os.replace to get cross-platform destination file overriding.
  • You should group standard library and third party imports separately.
  • If you're on Python 3.4+ you could also use pathlib library.

import os
from pathlib import Path

import xlrd


def rename_excel_files(path):
    path = Path(path)

    for filename in os.listdir(path):
        file_path = path.joinpath(filename)
        excel_file = xlrd.open_workbook(file_path)
        work_sheet = excel_file.sheet_by_index(0)
        dest_path = path.joinpath(work_sheet.cell_value(4, 2) + '.xlsx')
        os.replace(file_path, dest_path)

if __name__ == '__main__':
     rename_excel_files(r"C:\Users\Stewie\Documents\excel_folder")
\$\endgroup\$
1
  • \$\begingroup\$ I think os.replace(file_path, dest_path) could be replaced with file_path.replace(dest_path). \$\endgroup\$ Mar 17, 2018 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.