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.')