Pandas批量读取文件夹xlsx报错 加openpyxl后报BadZipFile如何解决

错误原因分析 #

• 报错Excel file format cannot be determined, you must specify an engine manually.是pandas 1.3.0版本对Excel格式自动识别的兼容性问题，当文件格式与后缀不匹配、或存在非标准Excel文件时会触发。
• 后续BadZipFile: File is not a zip file报错是因为遍历路径中存在后缀为.xlsx但实际不符合OpenXML格式的文件：最常见的是MacOS系统自动生成的.DS_Store隐藏文件、Excel打开文件时生成的~$开头的临时缓存文件，或是实际为xls格式但被强行修改后缀为xlsx的文件。

稳定批量读取实现方案 #

完整可运行代码如下：

import pandas as pd
import glob
import os

# 定义客户ID对应的列名列表
customer_id = ["ID","customer_id","consumer_number","cus_id","client_ID"]
df_list = []

for file_path in glob.glob("./*.xlsx"):
    # 过滤无效文件：跳过Mac隐藏文件、Excel临时缓存文件
    file_name = os.path.basename(file_path)
    if file_name.startswith('.') or file_name.startswith('~$'):
        continue
    # 增加异常捕获，避免单个文件异常导致整个任务中断
    try:
        # 优先使用openpyxl读取标准xlsx格式文件
        df = pd.read_excel(file_path, engine="openpyxl")
    except:
        try:
            # 降级兼容读取实际为xls格式的文件，需要提前安装xlrd==1.2.0版本
            df = pd.read_excel(file_path, engine="xlrd")
        except Exception as e:
            print(f"文件{file_path}读取失败，错误信息：{e}")
            continue
    # 列名对齐处理
    df = df.reindex(columns=customer_id).dropna(how='all', axis=1)
    df.columns = ["ID"]
    df_list.append(df)

# 合并所有有效数据
all_data = pd.concat(df_list, ignore_index=True)

补充说明 #

• 如需读取xls格式的文件，需要安装指定版本的xlrd依赖：pip install xlrd==1.2.0，更高版本的xlrd已移除对xls格式的支持。
• 对于打印提示读取失败的文件，可手动校验文件是否损坏、是否为真实有效的Excel格式文件。

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