How to Merge Hundreds of Excel Sheets into One CSV with Python Pandas

1. Introduction

A community member asked how to automate the merging of many .xlsx files, each containing six identical‑structure sheets (short‑term, ultra‑short‑term, usable work, report‑day result, daily result, dead zone) into combined sheets and a final CSV file.

2. Implementation

The first solution reads each workbook, extracts the six sheets, appends their data to a dictionary, concatenates the lists, and writes each combined sheet to a separate CSV file.

# short‑term, ultra‑short‑term, usable work, report‑day result, daily result, dead zone – 6 sheets stored separately
import os
import pandas as pd

folder_path = r'C:\Users\pdcfi\Desktop\data'
os.chdir(folder_path)

sheet_names = ['短期', '超短期', '可用功', '上报日结果', '日结果', '死区']
all_sheets_data = {name: [] for name in sheet_names}

for file in os.listdir(folder_path):
    if file.endswith('.xlsx'):
        file_path = os.path.join(folder_path, file)
        for sheet_name in sheet_names:
            try:
                sheet_data = pd.read_excel(file_path, sheet_name=sheet_name)
                all_sheets_data[sheet_name].append(sheet_data)
            except Exception as e:
                print(e)
                print(f"Skipping sheet {sheet_name} in {file}")

for sheet_name, data in all_sheets_data.items():
    combined_data = pd.concat(data, ignore_index=True)
    csv_file_path = os.path.join(folder_path, f"{sheet_name}.csv")
    combined_data.to_csv(csv_file_path, index=False, encoding='utf_8_sig')

print('合并工作完成！')

An image illustrating the intermediate result is shown below.

The optimized version reads each workbook once, aggregates all sheets into a dictionary of DataFrames, and writes the entire collection into a single Excel file with separate worksheets.

import pandas as pd
import os

# Read all .xlsx files and merge each sheet
folder_path = r'C:\Users\Desktop\data'  # replace with actual path

all_data = {}

for file_name in os.listdir(folder_path):
    if file_name.endswith('.xlsx'):
        file_path = os.path.join(folder_path, file_name)
        xls = pd.ExcelFile(file_path)
        for sheet_name in xls.sheet_names:
            if sheet_name not in all_data:
                all_data[sheet_name] = pd.DataFrame()
            sheet_data = pd.read_excel(file_path, sheet_name=sheet_name)
            all_data[sheet_name] = pd.concat([all_data[sheet_name], sheet_data], ignore_index=True)

# Save all merged data to a single workbook
output_excel = r"C:\Users\Desktop\总表.xlsx"  # replace with actual output path
with pd.ExcelWriter(output_excel, engine='openpyxl') as writer:
    for sheet_name, df in all_data.items():
        df.to_excel(writer, sheet_name=sheet_name, index=False)

print("数据已成功合并并保存到总表.xlsx。")

3. Conclusion

The provided scripts enable batch processing of hundreds of Excel files, automatically consolidating each specific worksheet into unified CSV files or a single comprehensive Excel workbook, demonstrating practical Python‑pandas automation for data‑heavy office tasks.