How to Auto‑Rename Excel Files with Python: A Step‑by‑Step Guide

Introduction

In a recent Python community chat, a user asked how to batch‑rename Excel files in a data folder based on a reference spreadsheet that maps file names to identifiers.

Solution

The following script reads 原始数据.xlsx with pandas, iterates over the Excel files in ./data/, extracts the month from the file name, looks up the corresponding identifier, builds a new name such as 1月份的业绩_DDDD202301.xlsx, and renames the file.

import os
import pandas as pd

# Read the reference file
df = pd.read_excel('原始数据.xlsx')

data_path = './data/'
files = os.listdir(data_path)
for file in files:
    if file.endswith('.xlsx'):
        file_name, file_ext = os.path.splitext(file)
        month = file_name[:2]
        id_num = df[df['文件名'] == file_name]['标识'].values[0]
        new_file_name = month + '份的业绩_' + id_num + file_ext
        os.rename(data_path + file, data_path + new_file_name)

Running the script successfully renames the files, as shown in the screenshots below.

Note: The original implementation extracts the month with [:2], which fails for October‑December; an upcoming article will present an improved method.

Conclusion

The example demonstrates a practical Python automation technique for batch processing Excel files, useful for routine office tasks.