6 Powerful Python Techniques to Batch Merge Excel Sheets Across Subfolders

1. Introduction

The author, a Python enthusiast, previously shared tutorials on splitting Excel into CSV files and merging multiple Excel sheets. A community member needed to merge many Excel files that contain multiple sheets, prompting the addition of two new methods to the existing four.

2. Background

A prior article listed four ways to batch‑merge all sheets from Excel files in a directory. Those methods were well received, and the author now adds two more solutions contributed by community experts.

3. Implementation

Method Five (by "韩峰")

The following script walks through a target directory, reads every sheet from each Excel file, concatenates the data, and writes the combined result to a CSV file.

# -*- coding: utf-8 -*-
import pandas as pd
import os

path = r'E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file\777'
file_list = []
for root, dirs, filename in os.walk(path):
    for file in filename:
        file_list.append(path + '\\' + file)

All_data = pd.DataFrame()
for Prowler in file_list:
    ereader = pd.ExcelFile(Prowler)  # read file
    one_sheet_name = ereader.sheet_names  # list of sheet names
    for Sheet_Prowler in one_sheet_name:
        All_sheet_data = pd.read_excel(ereader, sheet_name=Sheet_Prowler)
        temp = pd.concat([All_data, All_sheet_data])
        All_data = pd.DataFrame(temp)

print(All_data)
All_data.to_csv(r'E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file\777\The_All_data.csv')

Method Six (by "🌑")

A concise one‑liner that uses pathlib and pandas to read every Excel (or XLSX) file under a folder, concatenate all sheets, and export the result to result.xlsx.

# -*- coding: utf-8 -*-
from pathlib import Path
import pandas as pd

path = Path(r'E:\PythonCrawler\python_crawler-master\MergeExcelSheet\file\888')
pd.concat([
    pd.concat(pd.read_excel(i, sheet_name=None))
    for i in path.glob("[!~]*.xls*")
], ignore_index=True).to_excel("result.xlsx", index=False)
print('合并完成!')

Both scripts were tested successfully; the second method is especially compact but may require installing the xlrd package for older Excel formats.

4. Summary

The article presents six practical Python solutions for batch‑merging all sheets from Excel files located in multiple subfolders, dramatically reducing manual effort and error risk. Readers are encouraged to adapt the code, package it as an executable with PyInstaller, and share feedback.