logo标签列表

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

excelcsvimport
621

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

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

146
当一个拥有1万个用户名的文件中出现一个名为“april25”的用户名时，它会被转换成日期格式，最终处理成“apr-25”，导致了“未找到用户名”的错误。这是因为你没有预料到Excel会将一个单一的值转换为日期，而在文件的前4000条记录中保留其余文本。多么糟糕的CSV读取代码啊！实际上，它不应该根据前X条记录来猜测类型并坚持使用吗？或者将所有内容都保留为文本。如果我想将其格式化为“通用”格式，我可以稍后选择。通过从一开始就假定为“通用”格式，它可能会导致文本数据损坏。 - Triynko
104
我认为Excel的这种行为是一个严重的缺陷。那么，对于所有没有奢侈改变CSV文件内容的人怎么办呢？或者对于在对CSV文件做了许多其他更改之后才意识到这个问题的人怎么办呢？这使得使用Excel处理CSV文件变得非常混乱。 - robguinness
你是否正在使用DatatableJS？因为我已经知道如何使用该API来完成这个任务。如果你需要，可以在这里找到它：https://dev59.com/LIDba4cB1Zd3GeqPDFqk#36142043 - Richard Rebeco
17
对于我们这些知道该怎么做的人来说，使用文件->打开->导入这些解决方案还不错，但对于那些不了解如何从应用程序/内部/浏览文件系统的全球其它99.5%的人来说，这是无用的。他们看到一个文件，想要使用它就双击它即可。我花费了25年时间教授人们如何使用办公应用程序并编写生成所述办公应用程序数据的代码，但使用/应用程序/查找要使用的文件对于几乎所有人来说都是完全超出了他们的理解能力。 - user2624417
我也遇到了这个问题。可以肯定的是 Excel 不是为开发人员设计的。我正在寻找替代方案。 - PathToLife
显示剩余4条评论
37个回答
2

在我的情况下，使用R生成的csv文件中的“Sept8”被Excel 2013转换为“8-Sept”。通过使用xlsx包中的write.xlsx2（）函数生成xlsx格式的输出文件，可以解决这个问题，该文件可以被Excel加载而不会发生不必要的转换。因此，如果您有一个csv文件，可以尝试将其加载到R中，并使用write.xlsx2（）函数将其转换为xlsx。

我不知道为什么你的回答被踩了。至少对于使用R的人来说是有用的。这个回答帮助了我。谢谢 :) - sriharsha KB
1

最简单的解决方案

  • 在Word中打开
  • 将所有连字符替换为短划线
  • 保存并关闭
  • 在Excel中打开

编辑完成后，您可以随时再次在Word中打开它，以再次将短划线替换为连字符。

1

(EXCEL 2016及更高版本，我没有在旧版本中尝试过)

  1. 打开新的空白页面
  2. 转到“数据”选项卡
  3. 单击“从文本/CSV导入”，选择您的CSV文件
  4. 在预览中检查数据是否正确。
  5. 如果某一列被转换为日期，请单击“编辑”，然后通过单击列标题上的日历选择“文本”类型
  6. 单击“关闭并加载”
1
如果有人还在寻找答案，下面的代码对我来说完美地解决了问题。
我输入了=("my_value")。即=("04SEP2009") 显示为04SEP2009 而不是 09/04/2009 对于超过15位数的整数也同样适用。它们不再被裁剪了。
1
如果您可以更改文件源数据
如果您准备修改原始的CSV文件，另一种选择是在数据中更改“分隔符”，因此如果您的数据是“4/11”（或4-11），而Excel将其转换为4/11/2021（英国或11-4-2021美国），那么将'/'或'-'字符更改为其他字符将阻止不需要的Excel日期转换。选项可能包括：
~（波浪号） +（加号） _（下划线） --（双破折号） En-dash（Alt 150） Em-dash（Alt 151） （其他一些字符！）
注意：如果文件将在其他地方使用，则转移到Unicode或其他非ascii / ansi字符可能会使问题复杂化。
因此，“4-11”用波浪号转换为“4〜11”将不被视为日期！
对于大型CSV文件，这种方法没有额外的开销（即：额外的引号/空格/制表符/公式结构），并且当文件直接打开时（即：双击CSV文件打开）就可以使用，避免了将列预格式化为文本或将CSV文件“导入”为文本。
如果需要，Notepad（或类似工具）中的搜索/替换可以轻松地转换为/从备用分隔符。
导入原始数据
在较新版本的Excel中，您可以导入数据（在其他答案中概述）。
在旧版本的Excel中，您可以安装“Power Query”插件。此工具还可以导入CSV而无需转换。选择：Power Query选项卡/从文件/从文本-CSV，然后“加载”以打开为表格。（您可以从“数据类型检测”选项中选择“不检测数据类型”）。
1
使用Google Drive（或Mac上的Numbers）的解决方法：
  1. 在Excel中打开数据
  2. 将包含不正确数据的列的格式设置为文本（格式 > 单元格 > 数字 > 文本）
  3. 将.csv文件加载到Google Drive中，并使用Google Sheets打开它
  4. 复制问题列
  5. 将该列作为文本粘贴到Excel中（编辑 > 粘贴特殊 > 文本）
另外，如果您使用Mac，在第3步中可以在Numbers中打开数据。
这是一个明智的解决方案，而不是花费数小时而毫无结果。 - Mr. Unnormalized Posterior
0
我为信用卡号码做这件事情，因为它们总是转换成科学计数法：最终我将我的 .csv 文件导入到 Google Sheets 中。现在的导入选项允许禁用数字值的自动格式化。我将任何敏感列设置为纯文本，并下载为 xlsx。
这是一个可怕的工作流程，但至少我的值保持了应有的样子。
1
我已经理解了，谢谢。我的意思是自动格式选择是导入过程的一个新部分。我的答案提供了一个非常具体的工作流程，适用于像我一样被迫使用 Sheets 处理这个特定问题的人。在我看来，这值得单独回答。稍微调整一下你的语气可能会让你的评论被解释为有帮助的，这也是这个网站家族的全部目的。 - Brendonwbrown
但是，正如其他答案中所指出的那样，您可以只使用Excel中的文本导入功能来导入CSV文件，而不必使用Sheets。 - NetMage
这个信用卡安全系统是否符合PCI/DSS标准？你应该与谷歌分享信用卡号码吗？ - andora
0

好的，在Excel 2003到2007中，我找到了一个简单的方法来完成这个任务。打开一个空白的xls工作簿。然后进入数据菜单，导入外部数据。选择你的csv文件。按照向导进行操作，然后在“列数据格式”中选择需要强制转换为“文本”的任何列。这将把整个列作为文本格式导入，防止Excel尝试将任何特定单元格视为日期。

0

这个问题在Mac Office 2011和Office 2013中仍然存在，我无法防止其发生。这似乎是一个基本的事情。

在我的案例中，CSV文件中正确使用引号括起来的值（例如“1-2”和“7-12”），Excel会自动将其转换为日期。如果您尝试随后将其转换为纯文本，则会得到日期的数字表示，例如43768。此外，它还会重新格式化条形码和EAN编号中的大数值为123E+数字，无法再次转换回去。

我发现Google Drive的Google Sheets不会将数字转换为日期。条形码中确实每3个字符有逗号，但这些很容易删除。在处理MAC / Windows CSV时，它非常擅长处理CSV文件。

可能会为某些人节省时间。

-1
将表格复制到 Word 中。进行搜索/替换并将所有 -（破折号）更改为--（双破折号）。复制并粘贴到 Excel 中。可以对其他符号（/）等执行相同的操作。如果需要在 Excel 中将其更改回破折号，只需将列格式设置为文本，然后进行更改即可。希望这能帮到您。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接