Automate Folder Renaming With Python and Excel

Automate folder renaming with Python and Excel to make managing data and keeping your files organized more efficient in today’s digital age. Handling vast amounts of data as a professional or simply maintaining personal files in order can be time-consuming and complex. To address this challenge, I’ve developed a comprehensive step-by-step guide that demonstrates how to harness the power of Python and Excel for seamless folder renaming and organization.

Table of Contents

Some of the things mentioned in this post I learned from the Zerto To Mastery academy here. If You decide to sign-up, I will earn a commission.

The real-world business problem

Before I get into the actual Python code – it is time to understand our problem better.

I recently ran into a business situation where users were getting data out of one of their applications. The data was formatted in a way that it wasn’t supporting the business logic anymore. And the application hadn’t been updated – thus creating a scenario where users had to do a lot of manual labor.

This meant going through folders one by one and renaming them. Boring, tedious, and unnecessary work.

*Imagine over 600 of these types of folders – just waiting to be renamed one by one…*

To make the issue even more clear:

We have over 600 folders that have to be renamed using certain business logic.
This logic is luckily in an Excel spreadsheet.
The folders are named after a column in that spreadsheet.
The business logic that we have to use for the renaming of the folders exists in that same spreadsheet and in the same table. And is connected to the value of the folder (crucially important).
We have to take data from the first 2 columns in the Excel spreadsheet > match it with the value of the folder using the 3rd column in the spreadsheet > and then automatically change the names of the folders.

This is what the Excel file looked like. Remember, we want to change the value3 or the values in column number 3 into a combination of value1 and value2. Or The folder named 123456 should be Important Data1 after the script runs.

A handful!

Enter Python to automate folder renaming

*Writing Python and writing about Python code has given me the excuse to generate AI art with a lot of snakes.*

I already shared why it’s good to use Python when working with Excel files in this post. Go read it now for a better understanding of the situation.

I’m not going to go through the necessary modules You need and how to set it up because You can read about it in the aforementioned post as well.

Let’s dive straight into the code!

Breaking down the script line by line to automate folder renaming with Python and Excel

*This is like a strange space Python – probably knows a lot about data.*

I will share the full code block at the end of the post. Let’s dissect it line by line first.

Import the necessary libraries:

import os
import pandas as pd
import logging

Configure logging settings to display messages with a level of INFO or higher, along with the time, log level, and message content:

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")

NB! This part is not necessary! I only needed it to troubleshoot some issues in our specific environment.

Working with Excel and defining the folder path where our other folders are

Define the path to the Excel file and read its content into a Pandas DataFrame:

excel_file = r"C:\nameyourexcelfile.xlsx"
logging.info(f"Reading Excel file: {excel_file}")
df = pd.read_excel(excel_file)

Set the path to the folder containing the subfolders to be renamed:

nimi_folder = r"C:\named"

Iterate through the rows of the DataFrame:

for index, row in df.iterrows():

Extract the values for value1, value2, and value3 columns from the current row:

    value1 = row["value1"]
    value2 = row["value2"]
    value3 = row["value3"]

Define the old folder name (based on the value3 column) and the new folder name (a combination of value1 and value2 columns). I personally needed the combination but You can add things to the name here as required. The business logic demanded a space between the two values:

    old_folder_name = value3
    new_folder_name = "{} {}".format(value1, value2)

Renaming the folders with Python

*Don’t be surprised when coding with Python when something like this happens.*

Set the paths for the old and new folder names:

    old_folder_path = os.path.join(nimi_folder, old_folder_name)
    new_folder_path = os.path.join(nimi_folder, new_folder_name)

Check if the old folder exists, and if so, rename it to the new folder name:

    if os.path.exists(old_folder_path):
        logging.info(f"Renaming folder '{old_folder_name}' to '{new_folder_name}'")
        os.rename(old_folder_path, new_folder_path)

Log a warning if the old folder is not found. (This part You can skip!):

    else:
        logging.warning(f"Folder '{old_folder_name}' not found")

*After Python has done it’s scripting magic – You should have something like this!*

The full Python code block to automate renaming folders

There You have it! It didn’t take me half a day to automate folder renaming with Python and Excel.

If we would have had to do it manually – it would have taken a day or more to rename all these files! Work that could be easily automated!

import os
import pandas as pd
import logging

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s")

# Read the Excel file
excel_file = r"C:\nameyourexcelfile.xlsx"
logging.info(f"Reading Excel file: {excel_file}")
df = pd.read_excel(excel_file)

# Set the path to the "named" folder on the C: drive
nimi_folder = r"C:\named"

# Iterate through the rows of the dataframe (Your Excel file, add as many values as You need #personally)
for index, row in df.iterrows():
    # Get the values for value1, value2, and value3
    value1 = row["value1"]
    value2 = row["value2"]
    value3 = row["value3"]

    # Define the old and new folder names (You can see that I was using the value from the 3 #column as the common denominator
    old_folder_name = value3
    new_folder_name = "{} {}".format(value1, value2)

    # Set the old and new folder paths
    old_folder_path = os.path.join(nimi_folder, old_folder_name)
    new_folder_path = os.path.join(nimi_folder, new_folder_name)

    # Check if the old folder exists and rename it
    if os.path.exists(old_folder_path):
        logging.info(f"Renaming folder '{old_folder_name}' to '{new_folder_name}'")
        os.rename(old_folder_path, new_folder_path)
    else:
        logging.warning(f"Folder '{old_folder_name}' not found")