如何递归生成并保存CSV至指定目录？解决Excel转CSV的XLRDError

核心错误原因 #

1. XLRDError报错：提示Expected BOF record; found b'\xef\xbb\xbf;data'，说明你处理的文件并非标准.xls格式，大概率是后缀被修改为.xls的CSV文件（\xef\xbb\xbf是UTF-8 BOM头，;data是CSV分隔符开头特征）。
2. 代码逻辑缺陷：
   • load_xlsx函数仅定义返回值，无实际读取Excel的实现代码，无法生成有效DataFrame。
   • 代码块2中，load_xlsx(fname)返回的是(events, meta)元组，你直接将元组赋值给filename并调用to_csv，属于类型错误。
   • 保存CSV时错误使用元组作为文件名，未提取原Excel文件名并替换后缀。

完整修复代码 #

第一部分：文件上传、解压与DataFrame加载 #

%%capture
import os
import numpy as np
import pandas as pd
import zipfile
from google.colab import files

# 安装依赖：xlrd支持旧版xls，openpyxl支持xlsx
!pip install xlrd==1.2.0 openpyxl

# 上传Zip压缩包
uploaded = files.upload()

# 获取上传的Zip文件名并解压
zipname = list(uploaded.keys())[0]
destination_path = 'files'
infolder = os.path.join('/content/', destination_path)

# 用Python zipfile模块替代shell命令，更可控
with zipfile.ZipFile(zipname, 'r') as zip_ref:
    zip_ref.extractall(destination_path)

# 修复load_xlsx函数：支持xls/xlsx，兼容伪装成xls的CSV
def load_xlsx(fullpath):
    if fullpath.lower().endswith('.xls'):
        df = pd.read_excel(fullpath, engine='xlrd')
    elif fullpath.lower().endswith('.xlsx'):
        df = pd.read_excel(fullpath, engine='openpyxl')
    else:
        # 识别伪装成xls的CSV文件，直接用CSV规则读取
        df = pd.read_csv(fullpath, sep=';', encoding='utf-8-sig')
    # 返回文件元信息（用于后续命名）
    meta = {'filename': os.path.basename(fullpath), 'fullpath': fullpath}
    return df, meta

# 遍历文件夹，收集所有Excel/伪装Excel的文件
tasks = []
for dp, dn, filenames in os.walk(infolder):
    for fname in filenames:
        if fname.lower().endswith(('.xls', '.xlsx')):
            tasks.append(os.path.join(dp, fname))

# 加载所有文件到DataFrame列表
dfs = []
metas = []
for fname in tasks:
    df, meta = load_xlsx(fname)
    dfs.append(df)
    metas.append(meta)

第二部分：DataFrame转CSV并保存 #

# 创建CSV保存目录
newpath = 'csv2021'
save_dir = os.path.join('/content/', newpath)
if not os.path.exists(save_dir):
    os.makedirs(save_dir)

# 遍历配对的DataFrame和元信息，保存为同名CSV
for df, meta in zip(dfs, metas):
    # 提取原文件名，替换后缀为.csv
    csv_filename = os.path.splitext(meta['filename'])[0] + '.csv'
    csv_fullpath = os.path.join(save_dir, csv_filename)
    # 保存CSV，保留指定编码和分隔符
    df.to_csv(csv_fullpath, encoding='utf-8-sig', sep=';', index=False)

第三部分：打包CSV文件夹并下载 #

# 打包CSV文件夹
import shutil
zip_output = 'csv_files.zip'
shutil.make_archive(zip_output.replace('.zip', ''), 'zip', save_dir)

# 下载压缩包
files.download(zip_output)

关键修复点说明 #

• 解决XLRDError：新增对伪装成xls的CSV文件的兼容处理，自动识别并调用pd.read_csv读取。
• 完善load_xlsx函数：补充文件读取逻辑，支持.xls和.xlsx格式，同时返回文件元信息用于后续命名。
• 修正CSV保存逻辑：通过元信息提取原文件名并替换后缀，确保CSV与原Excel同名；使用zip(dfs, metas)配对数据与文件信息，避免索引混乱。
• 替换shell解压方式：用Pythonzipfile模块替代!unzip，代码更跨平台，避免shell变量的潜在问题。

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