How to Automatically List All Excel Files in Subfolders with Python

1. Introduction

In the previous article a question was raised about handling Excel files located in multiple subfolders. This article answers that by demonstrating how to retrieve the paths of all Excel files using Python.

2. Implementation

The solution relies on the os and glob modules. First, import the modules:

import os
import glob

Then define the folder path, enumerate subfolders with os.scandir(), and collect Excel file paths using glob.glob():

import os
import glob

# Define folder path
folder_path = r"文件夹路径"

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

# Collect Excel file paths from each subfolder
excel_paths = []
for subfolder in subfolders:
    excel_paths.extend(glob.glob(subfolder + "/*.xlsx"))

The list excel_paths now contains the full paths of every Excel workbook found.

A more complete version adds pandas to read each workbook, set column headers, and save the changes:

import os
import pandas as pd
import glob

# Define 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()]

excel_paths = []
for subfolder in subfolders:
    excel_paths.extend(glob.glob(subfolder + "/*.xlsx"))

for excel_file in excel_paths:
    df = pd.read_excel(excel_file, header=None)
    df.columns = ["经度", "纬度"]
    df.to_excel(excel_file, index=False)

This script adds the specified headers to every Excel file in the subfolders.

3. Summary

The article presents a practical Python solution for automating the retrieval and processing of Excel files across nested directories, helping users efficiently manage and modify their data files.