高效合并Excel文件求助：100个50MB多工作表文件合并难题

针对你遇到的内存耗尽、SQLite列数过多问题，提供以下几个实用方案：

1. 逐表追加写入CSV/Parquet（最低内存占用） #

核心思路：不一次性加载所有数据，读取单个工作表后立即写入输出文件，写完即释放内存。同时统一列名，避免因列名不一致导致的列数爆炸。

import pandas as pd
import os

excel_dir = "./your_excel_dir"  # 替换为你的Excel目录路径
output_file = "./merged_data.csv"

first_write = True

for filename in os.listdir(excel_dir):
    if not filename.endswith((".xlsx", ".xls")):
        continue
    file_path = os.path.join(excel_dir, filename)
    # 读取当前文件的所有工作表（返回字典：{表名: DataFrame}）
    sheets = pd.read_excel(file_path, sheet_name=None, engine="openpyxl")  # .xls文件请改用engine="xlrd"
    
    for sheet_df in sheets.values():
        # 统一列名（去除空格、转小写、替换特殊字符，避免列名混乱）
        sheet_df.columns = [col.strip().lower().replace(" ", "_") for col in sheet_df.columns]
        # 写入文件：第一次写入带表头，后续追加时跳过表头
        sheet_df.to_csv(output_file, mode="a", index=False, header=first_write)
        first_write = False

如果需要更高效的存储，推荐将输出格式改为Parquet（支持压缩、列存储，后续处理速度更快），只需将to_csv替换为to_parquet（需提前安装pyarrow或fastparquet库）。

2. 用Dask处理超大数据量 #

Dask是专为大数据场景设计的并行计算库，API与Pandas兼容，自动将数据分块处理，不会一次性加载全部数据到内存，还能利用多核加速。

import dask.dataframe as dd
from dask.diagnostics import ProgressBar

# 读取目录下所有Excel文件的所有工作表
ddf = dd.read_excel(
    "./your_excel_dir/*.xlsx",
    sheet_name=None,
    engine="openpyxl",
    concat=pd.concat  # 自动合并不同工作表的数据
)

# 统一列名
ddf.columns = [col.strip().lower().replace(" ", "_") for col in ddf.columns]

# 写入Parquet文件（推荐）
with ProgressBar():
    ddf.to_parquet("./merged_data.parquet", write_index=False)

# 若需要CSV格式，可改用：
# with ProgressBar():
#     ddf.to_csv("./merged_data_part_*.csv", index=False)

3. 修复SQLite列数过多问题 #

之前的问题根源是不同工作表列名不一致，导致每次追加写入时SQLite自动新增列。解决方案是先统一所有列名，固定表结构后再写入。

import pandas as pd
import os
import sqlite3

excel_dir = "./your_excel_dir"
db_path = "./merged_data.db"
table_name = "merged_excel_data"

# 第一步：遍历所有文件，收集统一后的所有列名
all_columns = set()
for filename in os.listdir(excel_dir):
    if not filename.endswith((".xlsx", ".xls")):
        continue
    file_path = os.path.join(excel_dir, filename)
    # 仅读取表头（nrows=0），快速收集列名
    sheets = pd.read_excel(file_path, sheet_name=None, engine="openpyxl", nrows=0)
    for df in sheets.values():
        cols = [col.strip().lower().replace(" ", "_") for col in df.columns]
        all_columns.update(cols)
all_columns = sorted(all_columns)

# 第二步：创建固定结构的SQLite表
conn = sqlite3.connect(db_path)
# 所有列设为TEXT类型，避免数据类型不兼容问题
create_table_sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join([f'{col} TEXT' for col in all_columns])})"
conn.execute(create_table_sql)
conn.commit()

# 第三步：逐个读取工作表，补全缺失列后写入数据库
for filename in os.listdir(excel_dir):
    if not filename.endswith((".xlsx", ".xls")):
        continue
    file_path = os.path.join(excel_dir, filename)
    sheets = pd.read_excel(file_path, sheet_name=None, engine="openpyxl")
    for sheet_df in sheets.values():
        sheet_df.columns = [col.strip().lower().replace(" ", "_") for col in sheet_df.columns]
        # 补全缺失的列，空值填充为None
        sheet_df = sheet_df.reindex(columns=all_columns, fill_value=None)
        # 追加写入数据库
        sheet_df.to_sql(table_name, conn, if_exists="append", index=False)

conn.close()

内容的提问来源于stack exchange，提问作者Lukas