一堆 CSV 编码五花八门？一个脚本全并进一个 Excel：自动认编码、认分隔符、补齐缺列

2026-10-05 · 喵窝资源铺

从各系统导数据最常见的惨案：ERP 导出的是 GBK，CRM 导出的是带 BOM 的 UTF-8，运营手工存的是 UTF-8；有的用逗号，有的用制表符（Excel 里点「另存为」保存出来的就是）；列名一个系统叫「名字/电话」，另一个叫「姓名/手机号」。直接用 Excel 打开要么乱码要么全挤成一列。这篇给一个 只依赖 openpyxl 的合并脚本，文中代码实跑验证，真实输出贴在下面。

它解决的五件事

三个实跑才抓到的坑（比脚本本身更值钱）

坑1：双 BOM。测试文件我手写时在内容开头加了 \ufeff，又用 utf-8-sig 编码写盘——解码后开头又多出一个 BOM，表头变成 \ufeff姓名，别名匹配全部失效。修复：normalize_header 里 lstrip("\ufeff")，顺手免疫别人文件里多余的 BOM。
坑2：dict(zip(header, row)) 静默丢列。数据行字段数比表头多时（GB 表里「赵六…深圳,老客户」多一个备注），zip 按短的截断——末列数据无声消失，不报任何错。修复：先比长度，多出来的行在运行报告里写明「N行字段数超表头已截断」。
坑3：Sniffer 对混合分隔符误判。我自己造测试数据时手滑把「姓名/手机/城市」的表头写成了逗号分隔、数据行用 tab——真实世界就有这种烂文件。Sniffer 拿表头判成逗号，整行挤成一列。这不是脚本能救的（一行里两种分隔符本来就没法定义正确答案），但运行报告里的「分隔符'X'」+行数能让它立刻现形。

完整脚本

存为 csv_to_excel.py，运行 python csv_to_excel.py 输入目录 输出.xlsx（递归读子目录，空文件自动跳过）：

import csv, glob, io, os, sys
from openpyxl import Workbook

ALIASES = {
    "姓名": ["姓名", "name", "名字"],
    "手机号": ["手机号", "phone", "mobile", "电话", "手机"],
    "城市": ["城市", "city", "所在地"],
    "备注": ["备注", "note", "remark", "comment"],
}

def try_decode(raw: bytes):
    """按 BOM 和常见编码依次尝试，返回 (文本, 编码名) 或 (None, None)。"""
    if raw.startswith(b"\xff\xfe") or raw.startswith(b"\xfe\xff"):
        return raw.decode("utf-16"), "utf-16"
    for enc in ("utf-8-sig", "gbk", "gb18030"):
        try:
            return raw.decode(enc), enc
        except UnicodeDecodeError:
            continue
    return None, None

def sniff_dialect(text: str):
    """取第一个非空行的前 2000 字符做样本嗅探分隔符。"""
    sample = "\n".join(text.splitlines()[:3])[:2000]
    try:
        d = csv.Sniffer().sniff(sample, delimiters=",\t;|")
        return d.delimiter
    except csv.Error:
        return ","

def normalize_header(h):
    s = (h or "").strip().lstrip("\ufeff").strip().lower()
    for std, alts in ALIASES.items():
        if s in (a.lower() for a in alts) or s == std.lower():
            return std
    return s

def load_csv(path):
    raw = open(path, "rb").read()
    text, enc = try_decode(raw)
    if text is None:
        return None, None, "全部编码解码失败"
    delim = sniff_dialect(text)
    rows = list(csv.reader(io.StringIO(text), delimiter=delim))
    rows = [r for r in rows if any(c.strip() for c in r)]
    if not rows:
        return [], None, None
    header = [normalize_header(h) for h in rows[0]]
    data, ragged = [], 0
    for r in rows[1:]:
        if len(r) > len(header):
            ragged += 1          # 字段比表头多：dict(zip) 会静默丢末列，宁可报出来
            r = r[:len(header)]
        data.append(dict(zip(header, r)))
    meta = f"{enc}+分隔符'{delim}'"
    if ragged:
        meta += f"；{ragged}行字段数超表头已截断"
    return data, meta, None

def main():
    if len(sys.argv) != 3:
        print(__doc__); sys.exit(1)
    src, out = sys.argv[1], sys.argv[2]
    files = sorted(glob.glob(os.path.join(src, "**", "*.csv"), recursive=True))
    if not files:
        print("没找到 CSV"); sys.exit(1)

    wb = Workbook()
    wb.remove(wb.active)
    all_rows, report, used_names = [], [], set()

    for f in files:
        rel = os.path.relpath(f, src)
        data, meta, err = load_csv(f)
        if err:
            report.append((rel, "失败", err)); continue
        sheet_name = os.path.splitext(os.path.basename(f))[0][:28] or "sheet"
        base, i = sheet_name, 1
        while sheet_name in used_names:
            i += 1
            sheet_name = f"{base[:26]}_{i}"
        used_names.add(sheet_name)
        ws = wb.create_sheet(sheet_name)
        if data:
            cols = []
            for r in data:
                for c in r:
                    if c not in cols: cols.append(c)
            ws.append(cols)
            for r in data:
                ws.append([r.get(c, "") for c in cols])
            all_rows.extend(data)
        report.append((rel, f"{len(data)}行", meta))

    ws = wb.create_sheet("总表")
    cols = []
    for r in all_rows:
        for c in r:
            if c not in cols: cols.append(c)
    ws.append(cols)
    for r in all_rows:
        ws.append([r.get(c, "") for c in cols])

    wb.save(out)
    print(f"共 {len(files)} 个文件，总表 {len(all_rows)} 行 -> {out}")
    for rel, n, meta in report:
        print(f"  {rel}: {n}  {meta or ''}")

if __name__ == "__main__":
    main()

实测输出

造了 5 个故意做乱的 CSV：utf-8-sig（BOM）、GBK、tab 分隔、空文件、子目录里的重名文件；销售B部还有一行字段数超过表头。

共 5 个文件，总表 6 行 -> merged.xlsx
  empty.csv: 0行  
  华东.csv: 1行  utf-8-sig+分隔符'	'
  子目录/华东.csv: 1行  utf-8-sig+分隔符','
  销售A部.csv: 2行  utf-8-sig+分隔符','
  销售B部.csv: 2行  gbk+分隔符','；1行字段数超表头已截断

总表结果（6 行数据、4 列并集、缺列补空、GBK 与 BOM 文件的中文全部正常）：

← 返回喵窝资源铺 · 相关：多 Excel 合并（别名列名+乱日期+去重）