阻止Excel自动将某些文本值转换为日期

Question

请问是否有一种标记可以添加到我的csv文件中的某个字段，使得Excel不会尝试将其转换为日期格式？

我正在尝试从我的应用程序写入一个.csv文件，其中一个值看起来非常像日期，因此Excel自动将它从文本转换为日期格式。我尝试将所有文本字段（包括看起来像日期的字段）放在双引号内，但这没有任何效果。

- user16324

---

当一个拥有1万个用户名的文件中出现一个名为“april25”的用户名时，它会被转换成日期格式，最终处理成“apr-25”，导致了“未找到用户名”的错误。这是因为你没有预料到Excel会将一个单一的值转换为日期，而在文件的前4000条记录中保留其余文本。多么糟糕的CSV读取代码啊！实际上，它不应该根据前X条记录来猜测类型并坚持使用吗？或者将所有内容都保留为文本。如果我想将其格式化为“通用”格式，我可以稍后选择。通过从一开始就假定为“通用”格式，它可能会导致文本数据损坏。 - Triynko

我认为Excel的这种行为是一个严重的缺陷。那么，对于所有没有奢侈改变CSV文件内容的人怎么办呢？或者对于在对CSV文件做了许多其他更改之后才意识到这个问题的人怎么办呢？这使得使用Excel处理CSV文件变得非常混乱。 - robguinness

你是否正在使用DatatableJS？因为我已经知道如何使用该API来完成这个任务。如果你需要，可以在这里找到它：https://dev59.com/LIDba4cB1Zd3GeqPDFqk#36142043 - Richard Rebeco

对于我们这些知道该怎么做的人来说，使用文件->打开->导入这些解决方案还不错，但对于那些不了解如何从应用程序/内部/浏览文件系统的全球其它99.5%的人来说，这是无用的。他们看到一个文件，想要使用它就双击它即可。我花费了25年时间教授人们如何使用办公应用程序并编写生成所述办公应用程序数据的代码，但使用/应用程序/查找要使用的文件对于几乎所有人来说都是完全超出了他们的理解能力。 - user2624417

我也遇到了这个问题。可以肯定的是 Excel 不是为开发人员设计的。我正在寻找替代方案。 - PathToLife

Answer 1

我发现在双引号前面加上'='可以实现你想要的效果。它会强制数据变成文本。

例如：="2008-10-03", ="更多文本"

编辑（根据其他帖子）：由于Jeffiekins指出的Excel 2007错误，应该使用Andrew提出的解决方案："=""2008-10-03"""

- Jarod Elliott

---

我接受这个答案是因为：1）我的csv文件只会被Excel使用，2）这是用于会计目的，不能有一个单引号在开头，3）我不希望他们做任何导入操作，我只想让他们打开这个csv文件。 - user16324

太好了！但由于Excel 2007的一个bug，请使用Andrew提出的解决方案："=""2008-10-03"""。已更新帖子。 - Tomas

这个方法的原理是因为当Excel遇到等于号"="时，它会计算后面的表达式。在这种情况下，表达式只是一个字符串文字，而字符串文字的值就是字符串的内容。因此引号会自动消失。你可以输入=1+2+3，然后在CSV导入后得到值6。公式本身并不会在导入过程中被破坏。 - lokori

这种方法存在一个严重问题——如果你在Excel中打开文件并进行编辑，然后保存并再次打开，所有这些“”都会消失。 - ACV

现在这个时代，我们竟然不能仅仅告诉Excel不要更改日期格式，这真是太疯狂了。我的CSV文件因此带来了很多麻烦。必须使用像 ="xxx" 这样的不正规方法来解决问题，这应该不是解决方案。最多只能算是一个取巧，但微软应该努力改进他们的软件 :( - Andrew Newby

Answer 2

我知道这是一个老问题，但这个问题不会很快消失。CSV文件可以从大多数编程语言中轻松生成，它们相当小，在紧急情况下可以使用纯文本编辑器进行人类可读，并且无处不在。

问题不仅出现在文本字段中的日期，而且任何数字也会从文本转换为数字。以下是几个示例，其中这种情况会造成问题：

邮政编码 电话号码 政府身份证号码

它们有时可能以一个或多个零（0）开头，当转换为数字时被丢弃。或者该值包含可以与数学运算符混淆的字符（如日期：/，-）。

我能想到的两种情况“预先添加=”解决方案可能不理想：

文件可能被导入到除MS Excel之外的程序中（例如MS Word的邮件合并功能）， 人类可读性可能很重要。

我的解决办法

如果在值中预先/附加非数字和/或非日期字符，则该值将被识别为文本而不是转换。非打印字符很好，因为它不会改变显示的值。但是，普通的空格字符（\s，ASCII 32）对此无效，因为它被Excel切掉，然后该值仍然被转换。但是，还有其他各种打印和非打印空格字符可以很好地工作。但是，最简单的方法是在值后附加（添加）简单制表符字符（\t，ASCII 9）。

此方法的好处：

可从键盘或易于记忆的ASCII代码（9）中获取， 它不会干扰导入， 通常不会干扰邮件合并结果（取决于模板布局-但通常只会在行末添加一个宽空格）。 （如果这是问题，请查看其他字符，例如零宽度空格（ZWSP，Unicode U+200B）） 在Notepad（等）中查看CSV时不会造成太大阻碍， 可以通过Excel（或Notepad等）中的查找/替换删除， 您不需要导入CSV，而是可以双击以在Excel中打开CSV。

如果您不想使用选项卡，则可以在Unicode表中查找其他适当的内容。

另一种选择可能是生成XML文件，对于新版MS Excel版本也接受某种格式进行导入，并且允许类似于.XLS格式的更多选项，但我没有经验。

因此，有各种选择。根据您的要求/应用程序，其中一种可能比另一种更好。

补充

需要说的是，新版本（2013+）的MS Excel不再以电子表格格式打开CSV-使Excel的使用变得更加不方便...至少，存在绕过它的说明。例如，请参见此Stackoverflow：如何在Excel 2013中正确显示.csv文件？。

- frIT

---

在我的分隔符“;”后添加了“\t”后，我获得了正确的前导零...该死的微软办公套件，为什么这要花费我超过2分钟才能弄清楚？ - Morten Jensen

在所有值的末尾添加 \t 确实可以解决问题。这是一种 hacky 的解决方法，但在实践中它很好用。我更喜欢这种方法而不是使用等号 '=' 的公式技巧，因为前者可能在其他工具中难以处理。 - ocroquette

感激之情无法用赞来表达。在Notepad ++中使用搜索和替换将逗号“，”替换为制表符“\t,\t”（以适应第一列和最后一列），效果非常好。谢谢。 - Asaf

在我的MySQL查询中（通过PHP进行CSV输出），我使用了CONCAT('\t', column_name)。同样也起作用了。谢谢！ - user651390

很棒的想法。遗憾的是，Numbers比Excel要“聪明”一些，不会被标签技巧所欺骗。我能想到的最好的替代方法是使用一个前导 ' ，因为它是有点被理解且不太分散注意力的...但是一个看不见的字符会更好。 - Heath Raftery

Answer 3

在Jarod的解决方案和Jeffiekins提出的问题的基础上，您可以进行修改。

"May 16, 2011"

到

"=""May 16, 2011"""

- Andrew Ferk

---

如果文本长度超过一定限制，这个功能在Excel 2010中无法工作。 - steve

@Andrew，如果你需要在日期之间放置"值，这个“修复”将会导致问题。 - Pacerier

请注意，如果您保存Excel文件，等号符号将会消失，下次打开时就会回到原点。 - d512

我的经验与Steve和d512都不匹配。它在Excel 2013的传入.csv中对我有效，在保存为.xlsx后也不会恢复。任何人都可以使用这一行数据轻松测试：“806676”，“无”，“41”，“=“May 16, 2011””，“100.00”，“False”。 - tbc0

@tbc0 请尝试使用"806676","None","41","=""05-16-2011""","100.00","False"，您会发现保存和重新加载会导致连字符文本丢失。 - NetMage

Answer 4

我遇到了类似的问题，这是帮助我解决问题而不必编辑csv文件内容的解决方法：

如果您有灵活性来将文件命名为其他名称，而不是“.csv”，您可以使用“.txt”扩展名来命名它，例如“Myfile.txt”或“Myfile.csv.txt”。然后，当您在Excel中打开它（不是通过拖放，而是使用“文件”->“打开”或“最近使用的文件”列表），Excel会提供一个“文本导入向导”。

在向导的第一页中，选择“分隔符”作为文件类型。

在向导的第二页中，选择“，”作为分隔符，并选择文本限定符，如果您已经用引号将值括起来。

在向导的第三页中，逐个选择每列并将每列分配为“文本”类型，而不是“常规”类型，以防止Excel破坏您的数据。

希望这可以帮助您或其他遇到类似问题的人！

- rainerbit

---

顺便说一下，我不是第一个发现这个的人。http://risingline.com/use-excel-read-csv-without-reformatting.php该网页还提供了方便的指示，您可以在向导的第三页中Shift+单击列来一起选择它们，然后再将每个列分配为“文本”类型。 - rainerbit

这可能适用于日期值，但似乎不适用于Excel解释为数字的内容。如果您的字符串全部是数字和逗号，“文本”格式将成为科学计数法格式。 - Michael

这个对我来说完美地运作了，除了将文件重命名为 .txt 外，没有对我的文件进行任何更改。谢谢。 - Kevin

不幸的是，Excel 只允许您对前 6 列执行此操作。如果您需要更多列，则更好的方法是复制 csv 的内容，创建一个空的 Excel 文件，选择所有列/行并将类型设置为文本，然后粘贴内容并使用“数据 | 文本到列”功能。 - nachocab

这是最佳解决方案，应该标记为答案。 - Rob

Answer 5

2018

这是我使用的唯一正确的方法（而且无需修改CSV文件）。

Excel 2010：

1. 创建新工作簿
2. 数据 > 从文本/CSV导入 > 选择你的CSV文件
3. 在弹出窗口中选择“分隔符”，然后单击“下一步>”
4. 勾选“逗号”选项，取消其他选项的勾选，然后单击“下一步>”
5. 在“数据预览”中，向右滚动到最右侧，然后按住Shift键并单击最后一列（这将选择所有列）。现在在“列数据格式”中选择“文本”单选按钮，然后单击“完成”。

Excel office365：（客户端版本）

1. 创建新工作簿
2. 数据 > 从文本/CSV导入 > 选择你的CSV文件
3. 数据类型检测 > 不检测

注意：截至我撰写此文时，Excel office365（Web版本）无法执行此操作。

- evilReiko

---

这个方法不需要事先更改CSV文件，因此我认为这应该是正确的答案。 - Jack

这看起来像是正确的答案。我非常期待。但在MacOS（office365）上，我无法选择所有列：在文本导入向导中显示的预览窗口中没有水平滚动条，因此我只能选择可见列。我尝试了快捷键（CMD+A或其他方法），但都没有用。选项“不检测”也不存在。我感到非常沮丧。 - normanius

Excel Office 365，选择“文件”，“选项”，“数据”。如果需要，可以勾选显示旧版数据导入向导的复选框。 - Kurt Schultz

正确的答案与否并不重要，谢谢。这正是我们大多数人真正需要的。这正是我在2023年所需要的。但愿我几年前就能想到这一点。 - nfdavenport

Answer 6

警告：Excel '07（至少）存在另一个错误：如果字段内容中有逗号，则无法正确解析 ="field，contents"，而是将逗号后的所有内容放入以下字段，而不管引号。

我发现唯一可行的解决方法是在字段内容包括逗号时消除 =。

这可能意味着有些字段在Excel中无法准确地表示“正确”，但现在我相信没有人会感到太惊讶了。

- Jeffiekins

---

我刚在Excel 2007中测试了一下，对我来说它可以正常工作。但是，如果引号前有一个前导空格，则逗号不会被正确解析。 - Sunny88

根据RFC 4180，这似乎不是一个错误。引用字段的正确方式是先引用整个字段，然后再在内部引用中加入双引号。因此，应该这样写："=""字段，内容""" http://tools.ietf.org/html/rfc4180 - Peter Stephens

@PeterStephens，如果field, content中包含"，这将无效。例如："=""field, co""ntent""" - Pacerier

@Pacerier 可能需要将嵌入的引用加上双引号，变成 """"。 - ErikE

我可以确认@ErikE的建议是正确的。对于一个值为1"2的字段，如果你选择使用这个技巧（而不是将其作为文本保留在文件中 - Excel已经将其视为文本），那么在指向Excel的CSV文件中，最终正确的字符串就是"=""1""""2"""。 - Tao

然而，在字段长度超过255个字符后，这个技巧就会失效！如果您真的非常需要这样做，那么您需要将长度超过255个字符的值分成多个子字符串，比如"=CONCATENATE(""1""""2"",""3"")"来表示值1"23。我似乎找不到一个可靠的方式来定位Excel，这是不可思议的。 - Tao

Answer 7

在使用C#创建要写入CSV文件的字符串时，我必须以这种方式格式化：

"=\"" + myVariable + "\""

- Colin Pear

---

这在Rails中对我也起作用。谢谢。 - Vieenay Siingh

可以翻译为：同样适用于Java。 - Shoyo

在 JavaScript 中使用 Papa Parse 对我有效。 - wobsoriano

Answer 8

在Excel 2010中打开一个新的工作表。 在数据选项卡上点击“从文本获取外部数据”。 选择您的CSV文件，然后点击“打开”按钮。 点击“下一步”按钮。 取消勾选“制表符”，在“逗号”旁边打勾，然后点击“下一步”按钮。 单击第一列的任意位置。 同时按住Shift键并拖动滑块，直到您可以单击最后一列为止，然后松开Shift键。 单击“文本”单选按钮，然后单击“完成”按钮

所有列都将被导入为文本，就像它们在CSV文件中一样。

- Rob Stockley

---

我遇到了同样的问题，但是仔细按照@Rob的指示操作会完美解决！谢谢。 - Milacay

很遗憾，“从文本获取数据”助手不支持多行单元格。如果遇到这样的多行文本，它会将第一行后的所有行写入新行，破坏整个导入过程。只有通过双击打开CSV文件时，它才能正确解释换行符。该死的微软... - CodeManX

不幸的是，这并没有解决 Excel 随机格式化的问题，即 文本 DEC1 仍然被更改为 日期 Dec-01。同样的，YYYY-MM-DD 的文本 被转换成 DD/MM/YYYY。 :( - Baracus

Answer 9

在Microsoft Office 2016版本中仍存在问题，对于我们这些处理基因名称（如MARC1、MARCH1、SEPT1等）的人而言，这相当令人不安。

我发现生成一个“.csv”文件，然后在Excel中打开/共享是最实用的解决方案:

1. 将CSV文件以文本格式打开（记事本）
2. 复制它（ctrl+a, ctrl+c）。
3. 将其粘贴到新的Excel工作表中——它将作为长文本字符串全部粘贴在一列中。
4. 选择此列。
5. 转到“数据-“文本到列...”，在打开的窗口中选择“分隔符”（下一步）。检查是否标记了“逗号”（标记它将显示下面数据的分列），在此窗口中，您可以选择所需的列并将其标记为文本（而不是常规）（完成）。

HTH

- Ana Maria Mendes-Pereira

---

手动操作对我来说也不太吸引人。然而，在尝试了手头上的每种可能性之后，这个方案是最为强大和可靠的。 - Ana Maria Mendes-Pereira

批处理仍然是必要的。 - Ana Maria Mendes-Pereira

这实际上是这里最好的答案，需要最少的工作。我希望你可以设置一些选项来强制它不解析文件。 - rollsch

你也可以将所有逗号替换为制表符，这样它就会自动分隔。 - rollsch

Answer 10

以下是我们在生成csv文件时使用的简单方法，它会略微更改值，因此不适用于所有应用程序：

在csv中给所有值加上一个空格。

Excel将从数字（如“ 1”，“ 2.3”和“-2.9e4”）中去掉此空格，但对于日期（如“ 01/10/1993”）和布尔值（如“ TRUE”），则保留该空格，防止它们被转换为Excel的内部数据类型。

它还可以防止读取时双引号被删除，因此使csv中的文本始终不被Excel更改，即使存在“3.1415”之类的文本，也要用双引号括起来并在整个字符串前加上空格，例如（使用单引号表示您要输入的内容）' "3.1415"'。然后在Excel中，您始终拥有原始字符串，只不过它被双引号包围并在前面加上一个空格，因此需要在任何公式等中考虑这些内容。

- Dan

---

这对我有用。我有一些像1-1-1这样的值，它们会自动转换为日期。正如提到的那样，空格将被传输，因此值会改变，但是当我解析时，我会对其进行剥离，所以对我来说这不是什么大问题。 - jacklin

空格不再起到防止日期解释或去除前导零的作用。但是你可以使用一个非断行空格 "\xA0" 代替 - 这样就可以了。 - Doin