CSV 在 Excel 中出问题的原因:编码、分隔符和消失的零
CSV 文件既不说明自己的编码,也不说明分隔符和各列的类型,所以双击打开时 Excel 会把这三样全都猜一遍。通过“数据”选项卡导入,或者转换成所有单元格都是文本的 .xlsx,就不需要猜测了。
ConvertBox发布
CSV 文件不带元数据,所以 Excel 只能猜
CSV 是纯文本:一行行用分隔符隔开的值。文件里没有任何地方说明用的是哪种字符编码,分隔符是逗号还是分号,00123 是数字还是代码。RFC 4180 描述了常见的写法,但并不是强制性的标准,实际导出的文件各不相同。双击 .csv 文件时,Excel 会根据 Windows 的区域设置和每个值的外观,不加询问地用猜测来回答这些问题。几乎所有关于 CSV 的抱怨,都能追溯到这三个猜测中的某一个。
乱码:字符编码与字节顺序标记(BOM)
现在多数系统都以 UTF-8 导出 CSV。但 Windows 上的 Excel 在双击打开文件时,只要文件不是以字节顺序标记(BOM)即 EF BB BF 这三个字节开头,就会假定它使用本地区的传统代码页,在简体中文 Windows 上是 GBK(CP936)。没有 BOM,所有非 ASCII 字符都会被读错:UTF-8 中一个汉字占 3 个字节,而 GBK 按每 2 个字节一个字来读,于是“上海”显示成“涓婃捣”。
反过来的问题同样存在。旧版 Excel 用本地代码页保存的 CSV 不是合法的 UTF-8,所以预期 UTF-8 的工具会显示替换符号。本站的 CSV 查看器和 CSV to Excel 转换工具只按 UTF-8 读取文件,传统编码的文件请先另存为“CSV UTF-8”格式。这两个工具下载的 CSV 是不带 BOM 的 UTF-8,在 Windows 版 Excel 中双击打开会出现同样的乱码;而 .xlsx 输出不存在这个问题,因为工作簿格式自己声明了编码。
全部挤在 A 列:分隔符与列表分隔符
Excel 并不默认使用逗号。它按 Windows 区域设置中的列表分隔符来拆分,而在德国、法国、西班牙等用逗号作小数点的地区,这个分隔符是分号。在那样的电脑上打开逗号分隔的文件,内容会全部落在 A 列;在中国或美国设置的电脑上打开分号分隔的文件也是一样。小数点符号遵循同一项设置,所以 1,5 在一台电脑上是一点五,在另一台电脑上是两个字段。
有一个只对 Excel 有效的变通办法:在第一行写上 sep=; 或 sep=,,告诉 Excel 使用哪个分隔符;其他程序会把这一行当作数据,所以不要把它放进给脚本读取的文件里。本站的 CSV 查看器通过检查开头几行,自动识别逗号、制表符、分号和竖线,下载的文件则始终以逗号分隔。CSV to JSON 转换工具让你自己在这四种分隔符中选择。
被 Excel 改写的值:前导零、长数字和日期
在默认的“常规”格式下,Excel 会转换一切看起来像数字或日期的内容。之后单元格里保存的是转换后的值,再另存为 CSV 时写入的也是这个值,原来的文本就找不回来了。
本站的 CSV 查看器不做类型转换,原样显示每个单元格的文本,所以用它可以很快确认文件里到底是什么内容。CSV to Excel 把每个单元格都写成文本,因此 00123 和 16 位数字都能保留;代价是数值列必须先执行“转换为数字”,SUM 才会把它们计算在内。CSV to JSON 的默认行为正好相反:它的自动类型转换即使遇到加了引号的值,也会把 007 变成 7,所以处理代码和长 ID 时要把这个选项关掉。
| 文件中的内容 | 在 Excel 中双击打开后 | 原因 |
|---|---|---|
| 00123 | 123 | 被当作数字读取,前导零不影响数值 |
| 1234567890123456 | 显示为 1.23457E+15,保存为 1234567890123450 | 数字只保留 15 位有效数字 |
| 1E5 | 1.00E+05 | 被当作 100000 的科学计数法 |
| 1-2 | 1 月 2 日这样的日期 | 看起来像当年的月和日 |
| MAR1 | 日期(3 月 1 日) | 月份缩写后面跟着数字 |
| 03/04/2024 | 3 月 4 日或 4 月 3 日 | 日和月的顺序取决于区域设置 |
字段里的引号、逗号和换行
包含分隔符、双引号或换行的字段必须用双引号括起来,字段内部的引号要写两次。7,"Smith, Ann","She said ""hi""" 这条记录有三个字段:7,然后是 Smith, Ann,然后是 She said "hi"。不加引号、直接用逗号把值拼起来生成的文件,只要地址或备注里第一次出现逗号,列就会错位。
引号内的换行是合乎规则的写法,Excel 中只要单元格里有用 Alt+Enter 输入的换行,保存时就会产生这种写法。这时一条记录在物理上跨了两行,所以任何按行处理的做法,比如统计行数或在脚本里按换行符拆分,看到的都是一条断开的记录。本站的 CSV 查看器会把这样的字段保留在一个单元格里。CSV to JSON 转换工具则不会:它在检查引号之前就先把文本拆成行,所以多行单元格会变成错位的行。
安全导入,而不是双击打开
有两条路可以避开猜测。一条是把 CSV 转换成所有单元格都是文本的 .xlsx。另一条是使用 Excel 自带的导入对话框,它会把双击时被悄悄回答掉的三个问题逐一问出来。在当前的 Windows 版 Excel 中,步骤如下。
- 打开一个空白工作簿,选择“数据”>“从文本/CSV”,而不是直接打开文件。
- 把“文件原始格式”设为 65001: Unicode (UTF-8),并在预览中确认中文显示正确。
- 选择文件实际使用的分隔符。
- 把“数据类型检测”设为“不检测数据类型”,或者选择“转换数据”,把代码、ID 和电话号码所在的列设为文本类型,然后加载。
- 保持原始 CSV 不动,把工作成果保存为 .xlsx;再存回 CSV 时,写入的是 Excel 当前保存在各单元格中的值。
生成在别人的软件里也不出问题的 CSV
如果文件由你生成,几个选择就能避免下游的大部分麻烦。先清理数据:值两端多余的空格、完全空白的行和完全重复的行,在导入之前去掉比导入之后容易。本站的数据清理工具按固定顺序完成这三件事,去重时比较的是整行。
- 使用 UTF-8 编码。只有当接收方会在 Excel 中双击打开文件时才加 BOM;另一些解析器会把 BOM 读成第一个列名开头的一个不可见字符。
- 日期按 ISO 8601(2024-03-09)书写,任何区域设置都不会把它的顺序读错。
- 数字不加千位分隔符,小数点用句点,并把这一约定写进文档。
- 凡是可能包含分隔符、引号或换行的字段都加上引号,并让每一行的字段数相同。
- 如果接收方是 Excel 用户而不是程序,就发送 .xlsx,它会明确保存类型和编码。