Batch File Renaming with Python

Below is an example of how Microsoft Excel files can be renamed in a batch, based on the contents of a particular cell within each file. For this to work the third-party module ‘OpenPyXL‘ must be installed.

Firstly, the file path is set and a check is carried out to make sure that the path exists. The desired cell to extract the new file name is also set. This is followed by a check to make sure that there are files to process at the desired location.  Each file is then processed one by one. All files without a ‘.xlsx’ extension are ignored. If the value in the specified cell of the first sheet in each individual file is populated and it contains only valid characters then the file is renamed and a message is displayed confirming each name change, otherwise a message is displayed stating that a particular file name could not been changed. A count of the number of files that have been renamed is also displayed. Finally, a ‘try-except’ block is used to handle errors with opening, closing and renaming files.

# Import required modules
import openpyxl
import os
import re
import shutil

# File path
filePath = 'c:\\demo'

# Cell containing new file name
cellForFileName = 'A1'

# Check to see if the file path exists
if os.path.exists(filePath):

    # Change the current working directory
    os.chdir(filePath)

    # Check if there are any files in the chosen directory
    if len(os.listdir(filePath)) == 0:

        print('There are no files to rename')

    else:

        # Renamed file count
        filesRenamed = 0

        # Process the files at the path
        for filename in os.listdir(filePath):

            # Check if the file is an Excel file, excluding temp files
            if filename.endswith('.xlsx') and not filename.startswith('~'):

                try:

                    # Open the file and find the first sheet
                    workbook = openpyxl.load_workbook(filename)
                    worksheet = workbook.worksheets[0]

                    # Check if there is a value in the cell for the new file name
                    if worksheet[cellForFileName].value is not None:

                        # Check to see if the cell value is valid for a file name
                        fileNameCheck = re.compile('[^\w,\s-]')
                        if not fileNameCheck.search(worksheet[cellForFileName].value):

                            # Construct the new file name
                            newFileName = worksheet[cellForFileName].value + '.xlsx'

                            # Close the workbook
                            workbook.close()

                            # Rename the file
                            shutil.move(filename, newFileName)

                            # Output confirmation message
                            print('The file "' + filename + '" has been renamed to "'
                                  + newFileName + '".')

                            # Increment the count
                            filesRenamed += 1

                        else:

                            # Display a message saying the file could not be renamed
                            print('The file "' + filename + '" could not be renamed.')

                            # Close the workbook
                            workbook.close()

                    else:

                        # Display a message saying the file could not be renamed
                        print('The file "' + filename + '" could not be renamed.')

                        # Close the workbook
                        workbook.close()

                except PermissionError as e:

                    # Display a message saying the file could not be renamed
                    print('The file "' + filename + '" could not be renamed.')

        # Display a message regarding the number of files renamed
        if filesRenamed == 1:
            print(str(filesRenamed) + ' file has been renamed.')
        else:
            print(str(filesRenamed) + ' files have been renamed.')

else:

    # Display a message stating that the file path does not exist
    print('File path does not exist.')