One‑Line Python Fix to Batch Add Headers to Excel Files in Subfolders

1. Introduction

In the previous post we raised a question about handling Excel files with different extensions (.xls and .xlsx) located in various subfolders, and we needed a one‑step solution.

2. Implementation

The solution, based on a suggestion from ChatGPT, requires only a minor change to the code. The full script is shown below:

import os
import pandas as pd

# Define the root folder path
folder_path = r"C:\Users\pdcfi\Desktop\新建文件夹"

# Get all subfolder paths
subfolders = [f.path for f in os.scandir(folder_path) if f.is_dir()]

# Add headers to each Excel file and save
for subfolder in subfolders:
    # Get paths of all Excel files in the subfolder
    excel_paths = [f.path for f in os.scandir(subfolder) if f.is_file() and (f.name.endswith(".xlsx") or f.name.endswith(".xls"))]
    for excel_path in excel_paths:
        # Read the Excel file
        df = pd.read_excel(excel_path, header=None)
        # Add column headers
        df.columns = ['经度', '纬度']
        # Save the Excel file
        df.to_excel(excel_path, index=False)

The script first defines the target directory, then uses os.scandir() to retrieve all subfolders. For each subfolder it collects the paths of .xls and .xlsx files, reads them with pandas.read_excel(), assigns the column names 经度 and 纬度 , and finally writes the modified workbook back using to_excel().

3. Summary

This concise Python program automatically adds the required headers to every Excel workbook in the specified folder hierarchy, providing a quick, one‑step resolution to the original problem. Future posts will explore handling deeper nested subfolders.