Excel to CSV Conversion: Common Pitfalls and How to Avoid Them - CSV-X.com

我仍然记得那天，因为看似无害的 Excel CSV 导出而让我公司损失了 47,000 美元。那是 2016 年，我在一家中型金融服务公司的数据集成专员岗位上工作已经三年了。我们正在将客户记录迁移到新的 CRM 系统，我的任务是为导入准备 180,000 条客户记录。Excel 文件看起来完美无缺。在记事本中打开时导出的 CSV 也看起来完美。但是当导入在周六凌晨 3 点运行时，它破坏了我们 23% 的客户电话号码，以毫无意义的方式合并了地址字段，并把我们精心维护的日期格式变成了一个难以辨认的混乱。

恢复花了两周时间，涉及手动验证数千条记录，且要求我们将 CRM 启动推迟一个月。那次经历让我从一个认为“另存为 CSV”只需简单点击按钮的人，变成一个执着于了解 Excel 到 CSV 转换每一个细节的人。在过去的十一年中，我帮助了 40 多家公司避免类似的灾难，我见识到了这个过程可能出错的几乎所有方式。

这里是大多数人没有意识到的：Excel 和 CSV 文件本质上是不同的东西，而 Excel 的 CSV 导出功能做出了数十项假设，可能会悄悄破坏你的数据。我将带你了解我遇到的七个最常见的陷阱，并给你提供经过实践验证的策略来避免它们。

字符编码陷阱：当你的数据说错了语言

字符编码是 CSV 转换的无声杀手。在我的咨询实践中，我估计 60% 的“损坏的 CSV”问题回溯到编码问题。这为什么重要：Excel 通常以系统的默认编码保存 CSV 文件，在 Windows 中通常是 Windows-1252 或 ANSI。但大多数现代 Web 应用程序、数据库和数据处理工具期望使用 UTF-8 编码。

一旦你知道该寻找什么，症状是显而易见的。客户的名称像“José García”变成“José García”。货币符号变成问号或方框。欧洲语言中的重音字符变成乱码。我曾与一家医疗提供者合作，他们的病人记录包括来自 47 个不同国家的名字。当他们使用 Excel 的默认设置导出到 CSV 时，约有 8,000 名病人的名字包含损坏的字符。

解决方法要求理解 Excel 的“另存为 CSV”选项并不让你控制编码。相反，你需要使用“另存为”，然后从文件类型下拉菜单中选择“CSV UTF-8（逗号分隔）”。这个选项是在 Excel 2016 中添加的。对于早期的 Excel 版本，你需要使用变通方法：保存为 Unicode 文本，然后使用文本编辑器或脚本语言转换为正确的 UTF-8 CSV 格式。

但这里有一个陷阱，甚至让有经验的用户也感到困惑：Excel 的 UTF-8 CSV 选项在文件开头包含一个 BOM（字节顺序标记）。虽然这有助于某些应用程序识别编码，但会导致其他应用程序出现问题。我见过基于 Unix 的系统在处理 BOM 前缀文件时出错，将那前面的三个字节视为实际数据。如果你正在处理无法很好处理 BOM 的系统，你需要使用支持编码操作的文本编辑器去掉它，或者使用简单的脚本。

我的建议是：始终先用小样本文件测试你的 CSV 导入。导入 100 条记录，验证特殊字符是否正确显示，然后再继续进行完整数据集。这五分钟的测试为我的客户节省了无数小时的清理工作。

前导零大屠杀：当 Excel 决定你的数据应该是什么

Excel 的激进数据类型解释可能已经摧毁了更多的数据完整性，超过了任何其他单一功能。问题很简单但隐蔽：Excel 查看你的数据，并决定它应该是什么类型，通常将你希望作为文本处理的东西转换为数字。最常见的受害者？前导零。

“Excel 的‘另存为 CSV’按钮并不是数据导出工具——它是一个数据转换地雷区，对你的编码、分隔符和格式化做出无声的假设，可能在毫秒内损坏数千条记录。”

我曾与一家电信公司合作，他们维护着一个 340,000 条电话号码的数据库。这些号码中的许多都是以零开头的，因为在国际拨号代码和某些区域格式中这是很常见的。当他们将 Excel 电子表格导出为 CSV 时，每一个前导零都消失了。像“0412345678”的电话号码变成了“412345678”。像“02134”的邮政编码变成了“2134”。像“00456-B”的产品代码变成了“456-B”。

经济影响显著。他们的呼叫中心无法联系到 18% 的客户，因为电话号码不完整。他们不得不与备份系统进行交叉参考，手动重建数据，并实施新的验证程序。该项目消耗了 200 人时，并延迟了一个主要的市场营销活动。

后台发生的事情是，当你在 Excel 中打开 CSV 文件时，它会自动解释数据。以零开头的数字会转换为数字格式，从而去掉前导零。当你再保存为 CSV 时，这些零将永远消失。对于长数字串，如信用卡号码或账户 ID，也会发生同样的情况——Excel 将它们转换为科学计数法（1.23E+15），你会失去精度。

解决方案需要一种多管齐下的方法。首先，如果你在 Excel 中创建要导出的 CSV 数据，在输入数据之前将这些列格式化为文本。右键单击列，选择格式单元格，然后选择文本。这告诉 Excel 将所有内容视为文字，从而保留前导零，防止科学计数法。

其次，如果你打开现有的 CSV 文件进行编辑，不要直接双击它。相反，先打开 Excel，然后使用数据选项卡的“从文本/CSV”导入向导。这使你能够控制每列的解释方式。你可以指定某些列应被视为文本，从而保留它们的原始格式。

第三，考虑一下你是否真的需要在 Excel 中打开 CSV。对于简单的编辑，文本编辑器可能更安全。对于复杂的转换，像 Python 这样的脚本语言或专业的 CSV 编辑器让你在没有 Excel 的“智能”自动转换的情况下获得更多控制。

分隔符困境：当逗号不是逗号

CSV 中的“C”代表“逗号”，但这里有一个导致无尽混淆的秘密：Excel 并不总是使用逗号作为保存 CSV 文件的分隔符。相反，它使用系统的列表分隔符设置，这因地区而异。在美国，它是逗号。在许多欧洲国家，它是分号。在某些地区，它是制表符。

我在为一家在 12 个国家设有办事处的跨国公司提供咨询时，吃到了这个苦头。他们的德国办公室导出了一个 CSV 文件，而他们的美国办公室无法正确导入。该文件在 Excel 中打开得很好，但当导入到他们的数据库系统时，每一行变成了一个单独的字段。问题是？德国系统使用分号作为分隔符，但美国导入工具期望使用逗号。

这个问题影响了我参与的约 30% 的国际数据传输。症状各异：有时导入完全失败，有时成功但所有数据都放在第一列，有时会产生奇怪的字段拆分，其中数据中的逗号被解释为分隔符。

根本原因是 Excel 的 CSV 导出使用 Windows 区域设置列表分隔符。你可以通过转到控制面板 > 区域 > 附加设置来检查你的设置。但更改这个系统范围的设置会影响其他应用程序，并且对大多数用户而言不是一个实际的解决方案。