Ensure

为什么你的 CSV 在 Excel 里打开是乱的

逗号、分号和制表符在各地区的差异，以及引号、转义、UTF-8 BOM，还有一套判断文件到底用哪个分隔符的可靠办法。

Excel 并不会去看 .csv 文件的内部来判断它是怎么分隔的。你双击打开时，它用的是操作系统区域设置里的列表分隔符，如果那个分隔符和文件对不上，每一行就会全部挤进一列。

在英国和美国，列表分隔符是逗号。在欧洲大陆大部分地区和拉丁美洲的很多国家，小数点写作逗号，于是列表分隔符是分号。所以一个从伦敦寄出的、完全合法的逗号分隔文件，在阿姆斯特丹、柏林或马德里打开就是挤成一列；分号文件反过来也一样。什么都没有损坏，只是文件被用错误的假设读取了。

三种正确打开它的方式

  1. 用导入，而不是直接打开。 在 Excel 里选“数据”，然后是“获取数据”或“从文本/CSV”，在对话框里指定分隔符和编码。这是唯一能让你完全掌控的途径，也是唯一能经受住寄往另一个国家的途径。
  2. 把文件改名成 .txt。 对 .txt 文件 Excel 拒绝去猜，而是弹出导入向导，问你分隔符是什么。
  3. 在发送之前把文件转换成对方期待的分隔符。 如果你知道收件人处在使用分号的区域设置里，就给他们发一个分号文件。

sep= 那一行

Excel 和 LibreOffice 都认识一个特殊的首行，它会覆盖区域设置：

sep=;
name;email;country
Ada;ada@example.com;UK

它确实管用，也是“客户打不开我导出的文件”的常见解法。但要知道它是电子表格软件的一个约定，并不属于任何 CSV 规范。把同一个文件喂给脚本或数据库导入工具，sep=; 会被当成普通的第一行，通常变成一个莫名其妙的列名。它适用于给拿着电子表格软件的人看的文件，不适用于机器与机器之间的交换。

引号与转义

CSV 的非正式参考是 RFC 4180，它描述的是通行做法，而不是强制规定。它的规则很短：

  • 如果一个字段包含分隔符、双引号或换行，就必须用双引号把它包起来。
  • 引起来的字段内部的双引号要写两遍。
id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

第 1 行有三个字段，不是四个，因为那个逗号在引号里面。成对的双引号在解析时会折叠成一个。第 2 行在一个引起来的字段内部包含了一个真实的换行，这是合法的，也正是你不能靠数行数来可靠地切分 CSV 的原因。

反斜杠转义（\, 或 \"）是另一种约定，某些数据库工具会用。它不属于 RFC 4180，严格的解析器看不懂，所以如果你的文件用了反斜杠，请明确告知读取它的那一方。

现实中最常见的损坏，是用字符串拼接写出来的文件，其中某个含逗号或者带了个游离引号的值根本没有被引起来。这会产生字段数多于表头的行，而这正是校验工具会指出来的那类问题。

BOM，以及带重音的字符为什么会变成乱码

字节序标记（BOM）是文件最开头三个不可见的字节，用来标明它是 UTF-8。Windows 上的 Excel 历来依赖它：没有 BOM 时，它可能退回到某种老旧的区域编码，于是 Müller 显示成 Müller，café 显示成 café。

代价是别的解析器并不总会把它剥掉。如果你的脚本报告第一列叫 \ufeffid 而不是 id，或者对 "id" 的查找莫名其妙只在第一列失败，那就是 BOM 在捣鬼。用 utf-8-sig 编码读取文件，它就消失了。

经验法则是：给要在 Excel 里打开的文件加上 BOM，给要由软件解析的文件不加。

行尾符

Windows 上的工具在每行末尾写 \r\n，Unix 上的工具写 \n。多数解析器两种都能应付，但按 \n 粗暴地拆分，会让每一行最后一个字段都粘上一个回车，这就是为什么一个看起来是 UK 的值在比较时死活不等于 "UK"。

怎么判断一个文件到底用了哪个分隔符

用纯文本编辑器打开这个文件，绝不要用电子表格软件，因为电子表格软件已经先替你猜过了。然后：

  1. 看前两三行。哪个候选字符（逗号、分号、制表符、竖线）出现在明显是字段值的东西之间？
  2. 在若干行上分别数一数每个候选字符。真正的分隔符在每一行上出现的次数都相同，比列数少一个。一个在这一行出现七次、下一行只出现两次的字符是数据，不是结构。
  3. 数的时候要把引起来的部分排除在外。"Smith, Ada" 里的逗号是内容，而不加区分地把它数进去，正是让一个货真价实的逗号分隔文件看上去逗号数量不一致的原因。
  4. 留意小数逗号。欧洲的文件常常既有分号分隔符，又在数字内部有逗号，例如 Ada;1.234,56;UK。如果你看到逗号从来只出现在数字之间，那它们是小数点，不是分隔符。
  5. 下任何结论之前，先检查开头那几个字节里有没有 sep= 行或者 BOM。

制表符值得单独提一句。制表符分隔的文件通常是交给电子表格软件最稳妥的东西，因为真实的值里几乎不会出现制表符，而 Excel 通过导入途径处理得也很好。麻烦在于制表符是看不见的，所以一个有人用空格对齐了各列的文件看起来一模一样，解析出来却什么用都没有。

一旦知道了分隔符，转换这个文件就是机械劳动：用正确的定界符和引号规则读进来，再按目标端期待的方式写出去，或者把它转成 JSON，从此彻底绕开分隔符这个问题。