结论:格式没有优劣,只有读者与边界
类型丢失、编码错乱、容量截断这三类故障有一个共同点:导出时不报错,导入时也不报错,只有在几个月后有人翻数据时才发现。选型只需回答两个问题:谁读这份数据,以及它会不会撞上格式的硬边界。
第一个分岔:谁读这份数据
给人看(运营打开、销售核对、客服查询)—— 用 XLSX。单元格有类型、可以带公式、有多张表,打开即用,这是 CSV 给不了的。
给程序读(ETL、数据库导入、另一个服务消费)—— 规模大用 Parquet/Arrow,规模小用 XLSX。
只做中转(系统 A 导出、系统 B 导入)—— CSV 仍是最通用的交换格式,但必须显式约定编码与分隔符,并写进文档。
这个分岔决定后续细节:给程序读的 CSV 可以放心扁平化,因为没人会手工打开;给人读的 CSV 必须考虑 Excel 打开时的类型猜测。
类型丢失:CSV 只存字符串
CSV 的名字已经说明定位——分隔符分隔的值。它没有类型系统、没有多表概念、没有元数据,每个字段就是一串字符。
| 原始数据 | CSV 里的实际内容 | Excel 打开后 |
|---|---|---|
订单号 00123 |
5 个字符 | 123,前导零消失 |
日期 2024-06-01 |
10 个字符 | 日期值,按本机格式重排 |
布尔 true |
4 个字符 | 布尔值 |
| 64 位 ID | 19 位数字 | 科学计数法,精度丢失 |
最后一行不是显示问题,而是数据本身被改了:19 位数字被截成 15 位有效数字,无法还原。往返转换(导出再导入)会叠加这些损失。
Excel 转文本 与 Excel 转 JSON 的差别正在这里——JSON 有类型,纯文本没有。只要需要保类型,就不能用 CSV,三条出路:
- 改用 XLSX,最省心,但失去纯文本的可 grep 性;
- 把类型编进数据:日期写 ISO 8601 带时区,ID 写成字符串并在表头声明;
- 用 JSON Lines:每行一个 JSON 对象,类型完整且仍可流式处理。
编码与 BOM:乱码来自没有声明
同一个 Excel,在不同系统上另存的编码不同:中文 Windows 用 GBK,英文 Windows、macOS、Linux 用 UTF-8。再加上不带 BOM 的 UTF-8 在 Excel 双击时识别不可靠,于是两个方向都会翻车:
- Windows 导出 GBK 无 BOM → Linux 按 UTF-8 读 → 中文乱码;
- 导出 UTF-8 无 BOM → 对面用 Excel 打开 → 同样乱码。
共同点是都没有声明。稳健方案是导出统一用 UTF-8 with BOM(开头字节 EF BB BF),接收端先探测再解码:
raw = open('data.csv', 'rb').read()
if raw.startswith(b'\xef\xbb\xbf'):
text, encoding = raw[3:].decode('utf-8'), 'utf-8-sig'
else:
text, encoding = raw.decode('gbk'), 'gbk' # 明确声明,而不是猜
最后一行注释是关键:不要靠猜。GBK 与 UTF-8 在短文本上视觉接近,猜错的概率不低。分隔符也有地区差异:逗号、分号、制表符,法国 Excel 默认分号;RFC 4180 只认逗号,遇到分号时整个文件会被当成单列。
容量上限与静默截断
Excel 的限制是硬性的:
- 单表 1,048,576 行 × 16,384 列;
- 单元格最多 32,767 字符。
超过之后的行为取决于工具,这是最危险的地方:pandas 会抛异常,但某些导出库会静默截断,只写出前 1,048,575 行而用户毫无察觉。文件能打开、格式正确、数据少了最后一行——这种故障可能几个月都发现不了。
超出限制时:超过百万行换列式格式(见下节);只是表格太宽就拆成多张表或转长格式;需要人读且量不大,用 XLSX 分 sheet。如果体积只是被冗长的日期表示撑大,先缩短表示,再考虑换格式。
验证方法:导出后立刻比对源数据与产物的行数,而不是只看文件能否打开。
什么时候换 Parquet
Parquet 的价值在规模上来之后才显现:
- 列式存储:分析查询通常只用 2–3 列,Parquet 只读这些列;
- 压缩率高:同质量数据约为 CSV 的十分之一;
- 类型随文件保存:不存在 CSV 的类型丢失。
经验阈值是单文件超过百万行或超过 1GB。在此之前,人类无法直接 cat 查看内容、工具链更复杂,心智成本大于收益。中间态是 Arrow——内存中的列式格式,适合需要零拷贝转换的场景。
自查与验证
- [ ] 明确读者是人还是程序,据此选格式
- [ ] 需要保类型时不选 CSV(或把类型编进数据)
- [ ] 导出用 UTF-8 with BOM,读取先探测 BOM
- [ ] 交换文档写明编码与分隔符,不靠猜
- [ ] 导出后核对行数是否与源数据一致(防静默截断)
- [ ] 超过百万行时评估 Parquet
用同一份含前导零与日期的表格分别走 Excel 转文本 和 Excel 转 JSON,可以直接看到差异:纯文本输出里 00123 只是一串字符,下游用表格软件打开仍会被当成数字;JSON 输出里它保持字符串类型,还原时前导零还在。格式属性对照见 电子表格格式速查。