停止Excel自动将某些文本值转换为日期-Java 学习之路

445

有没有人碰巧知道是否有一个令牌我可以添加到我的csv某个字段，所以Excel不会尝试将其转换为日期？

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

30 回答

311

这里提供的解决方案都不是一个好的解决方案 . 它可能适用于个别情况，但仅限于您控制最终显示 . 举个例子：我的作品产生了他们卖给零售的产品清单 . 这是CSV格式，包含部分代码，其中一些以零开头，由制造商设置（不在我们的控制之下） . 拿走前导零，你可能实际上匹配另一个产品 . 零售客户希望列表以CSV格式存在，因为后端处理程序也是我们无法控制的，每个客户也不同，因此我们无法更改CSV文件的格式 . 没有前缀'='，也没有添加标签 . 原始CSV文件中的数据是正确的;当客户在Excel中打开这些文件时，问题就开始了 . 许多客户并不是真正精通计算机的人 . 他们可以打开并保存电子邮件附件 . 我们正在考虑以两种稍微不同的格式提供数据：一种是Excel Friendly（使用上面建议的选项添加一个TAB，另一种作为'master' . 但这可能是一厢情愿的想法，因为有些客户不会理解为什么我们需要这样做 . 同时我们继续解释为什么他们有时会在他们的电子表格中看到“错误的”数据 . 直到微软做出适当的改变，我认为没有适当的解决方案，只要一个人无法控制最终用户使用文件 .

回复于 2024-04-27T08:21:56+08:00
1

好的，在Excel 2003到2007中找到了一种简单的方法 . 打开一个空白的xls工作簿 . 然后转到数据菜单，导入外部数据 . 选择您的csv文件 . 完成向导，然后在“列数据格式”中选择需要强制为“文本”的任何列 . 这会将整个列导入为文本格式，从而阻止Excel尝试将任何特定单元格视为日期 .

回复于 2024-04-27T08:21:56+08:00
0

它不是Excel . Windows确实识别公式，数据作为日期和自动更正 . 您必须更改Windows设置 .

“控制面板”（ - >“切换到经典视图”） - >“区域和语言选项” - >选项卡“区域选项” - >“自定义...” - >选项卡“数字” - >然后更改符号根据你的要求 .

http://www.pcreview.co.uk/forums/enable-disable-auto-convert-number-date-t3791902.html

它可以在您的计算机上运行，如果这些设置没有更改，例如在客户的计算机上，他们将看到日期而不是数据 .

回复于 2024-04-27T08:21:56+08:00
2
使用Google Cloud 端硬盘的解决方法（如果您使用的是Mac，则使用Numbers）：
- 在Excel中打开数据
- 将包含错误数据的列的格式设置为文本（格式>单元格>数字>文本）
- 将.csv加载到Google Cloud 端硬盘中，然后使用Google表格将其打开
- 复制违规列
- 将列粘贴到Excel中作为文本（编辑>选择性粘贴>文本）
或者，如果您在Mac上执行第3步，则可以在Numbers中打开数据 .
回复于 2024-04-27T08:21:56+08:00
2

前缀 space in double quotes 解决了这个问题!!

我在其中一个.csv文件列中有 "7/8" 等数据，MS-Excel将其转换为"07-Aug" . 但是"LibreOffice Calc"没有问题 .

为了解决这个问题，我只是将空格字符（在7之前添加了空格）作为 " 7/8" 的前缀，它对我有用 . 这是针对Excel-2007测试的 .

回复于 2024-04-27T08:21:56+08:00
1

在我的例子中，使用R生成的csv文件中的“Sept8”被Excel 2013转换为“8-Sept” . 通过在xlsx包中使用write.xlsx2（）函数生成xlsx格式的输出文件解决了这个问题 . ，可以通过Excel加载而无需进行不必要的转换 . 因此，如果给你一个csv文件，你可以尝试将它加载到R中并使用write.xlsx2（）函数将其转换为xlsx .

回复于 2024-04-27T08:21:56+08:00

我知道这是一个老话题 . 对于像我这样的人，使用office 2013通过powershell com对象仍然有这个问题可以使用opentext method . 问题是这个方法有很多参数，有时是互斥的 . 要解决此问题，您可以使用this post中引入的invoke-namedparameter方法 . 一个例子是

$ex = New-Object -com "Excel.Application"
$ex.visible = $true
$csv = "path\to\your\csv.csv"
Invoke-NamedParameter ($ex.workbooks) "opentext" @{"filename"=$csv; "Semicolon"= $true}

不幸的是，我刚刚发现当单元格包含换行符时，此方法会以某种方式破坏csv解析 . csv支持这一点，但微软实现似乎有问题 . 它也以某种方式检测不到德国特定的字符 . 赋予它正确的文化并没有改变这种行为 . 所有文件（csv和脚本）都使用utf8编码保存 . 首先，我编写了以下代码以按单元格插入csv单元格 .

$ex = New-Object -com "Excel.Application"
$ex.visible = $true;
$csv = "path\to\your\csv.csv";
$ex.workbooks.add();
$ex.activeWorkbook.activeSheet.Cells.NumberFormat = "@";
$data = import-csv $csv -encoding utf8 -delimiter ";"; 
$row = 1; 
$data | %{ $obj = $_; $col = 1; $_.psobject.properties.Name |%{if($row -eq1){$ex.ActiveWorkbook.activeSheet.Cells.item($row,$col).Value2= $_ };$ex.ActiveWorkbook.activeSheet.Cells.item($row+1,$col).Value2 =$obj.$_; $col++ }; $row++;}

但这是极其缓慢的，这就是我寻找替代方案的原因 . 显然，Excel允许您使用矩阵设置一系列单元格的值 . 所以我用了算法this blog在多阵列中转换csv .

function csvToExcel($csv,$delimiter){
     $a = New-Object -com "Excel.Application"
     $a.visible = $true

    $a.workbooks.add()
     $a.activeWorkbook.activeSheet.Cells.NumberFormat = "@"
     $data = import-csv -delimiter $delimiter $csv; 
     $array = ($data |ConvertTo-MultiArray).Value
     $starta = [int][char]'a' - 1
     if ($array.GetLength(1) -gt 26) {
         $col = [char]([int][math]::Floor($array.GetLength(1)/26) + $starta) + [char](($array.GetLength(1)%26) + $Starta)
     } else {
         $col = [char]($array.GetLength(1) + $starta)
     }
     $range = $a.activeWorkbook.activeSheet.Range("a1:"+$col+""+$array.GetLength(0))
     $range.value2 = $array;
     $range.Columns.AutoFit();
     $range.Rows.AutoFit();
     $range.Cells.HorizontalAlignment = -4131
     $range.Cells.VerticalAlignment = -4160
}

 function ConvertTo-MultiArray {
     param(
         [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
         [PSObject[]]$InputObject
     )
     BEGIN {
         $objects = @()
         [ref]$array = [ref]$null
     }
     Process {
         $objects += $InputObject
     }
     END {
         $properties = $objects[0].psobject.properties |%{$_.name}
         $array.Value = New-Object 'object[,]' ($objects.Count+1),$properties.count
         # i = row and j = column
         $j = 0
         $properties |%{
             $array.Value[0,$j] = $_.tostring()
             $j++
         }
         $i = 1
         $objects |% {
             $item = $_
             $j = 0
             $properties | % {
                 if ($item.($_) -eq $null) {
                     $array.value[$i,$j] = ""
                 }
                 else {
                     $array.value[$i,$j] = $item.($_).tostring()
                 }
                 $j++
             }
             $i++
         }
         $array
     } 
} 
csvToExcel "storage_stats.csv" ";"

您可以使用上面的代码，因为它应该将任何csvs转换为excel . 只需更改csv的路径和底部的分隔符 .

回复于 2024-04-27T08:21:56+08:00

8

（假设Excel 2003 ...）

使用“文本到列”向导时，在步骤3中，您可以指定每个列的数据类型 . 单击预览中的列，将行为不当列从“常规”更改为“文本” .

回复于 2024-04-27T08:21:56+08:00
2
使用Jarod的解决方案和Jeffiekins提出的问题，你可以修改
```
"May 16, 2011"
```
至
```
"=""May 16, 2011"""
```
回复于 2024-04-27T08:21:56+08:00
10
在C＃中创建要写入我的CSV文件的字符串时，我必须以这种方式格式化：
```
"=\"" + myVariable + "\""
```
回复于 2024-04-27T08:21:56+08:00
55
最简单的解决方案我今天才想到这一点 .
- 在Word中打开
- 用短划线替换所有连字符
- 保存并关闭
- 在Excel中打开
完成编辑后，您可以再次在Word中重新打开它，再次使用连字符替换连字符 .
回复于 2024-04-27T08:21:56+08:00
119

这是我知道如何在不弄乱文件内部的情况下实现此目的的唯一方法 . 像往常一样，我通过在桌面上敲打几个小时来学习这一点 .

将.csv文件扩展名更改为.txt;这将阻止Excel在文件打开时自动转换文件 . 我是这样做的：打开Excel到空白工作表，关闭空白工作表，然后File => Open并选择扩展名为.txt的文件 . 这会强制Excel打开“文本导入向导”，它会询问您有关如何解释文件的问题 . 首先选择分隔符（逗号，制表符等），然后（这是重要的部分）选择一组列并选择格式 . 如果你想要文件中的确切内容然后选择“文本”，Excel将显示分隔符之间的内容 .

回复于 2024-04-27T08:21:56+08:00
2

在Excel 2010中打开一个新工作表 . 在“数据”功能区上，单击“从文本获取外部数据” . 选择您的CSV文件，然后单击“打开” . 点击下一步” . 取消选中“Tab”，在“逗号”旁边放置一个复选标记，然后单击“下一步” . 单击第一列上的任意位置 . 按住shift键的同时拖动滑块直到您可以单击最后一列，然后释放shift键 . 单击“文本”单选按钮，然后单击“完成”

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

回复于 2024-04-27T08:21:56+08:00
1
在Microsoft Office 2016版本中仍然是一个问题，对于我们这些使用基因名称的人来说相当令人不安，例如MARC1，MARCH1，SEPT1等 . 我发现在R中生成“.csv”文件后最实用的解决方案，然后将与Excel用户打开/共享：
- 以文本形式打开CSV文件（记事本）
- 复制它（ctrl a，ctrl c） .
- 将其粘贴到新的Excel工作表中 - 它将作为长文本字符串粘贴在一列中 .
- 选择/选择此列 .
- 转到Data- "Text to columns..."，在打开的窗口中选择"delimited"（下一步） . 检查"comma"是否已标记（标记它已经显示数据与下面列的分隔）（下一步），在此窗口中，您可以选择所需的列并将其标记为文本（而不是常规）（完成） .
HTH
回复于 2024-04-27T08:21:56+08:00
1

Mac Office 2011和Office 2013中仍然存在此问题，我无法阻止它发生 . 这似乎是一件基本的事情 .

在我的情况下，我在CSV中正确包含了“1 - 2”和“7 - 12”之类的值，这会自动转换为excel中的日期，如果您尝试将其转换为纯文本，您将获得日期的数字表示，例如43768.此外，它将条形码和EAN编号中的大数字重新格式化为123E数字，这些数字无法转换回来 .

我发现Google Cloud 端硬盘的Google表格并没有将这些数字转换为日期 . 条形码每3个字符中都有逗号，但这些字符很容易删除 . 它非常适合处理CSV，特别是在处理MAC / Windows CSV时 .

有时可能会救人 .

回复于 2024-04-27T08:21:56+08:00

嗨我有同样的问题，

我写这个vbscipt来创建另一个CSV文件 . 新的CSV文件将在每个字段的字体中有一个空格，因此excel会将其理解为文本 .

因此，您使用下面的代码创建.vbs文件（例如Modify_CSV.vbs），保存并关闭它 . Drag and Drop 您的原始文件到您的vbscript文件 . 它将在同一位置创建一个文件名为"SPACE_ADDED"的新文件 .

Set objArgs = WScript.Arguments

Set objFso = createobject("scripting.filesystemobject")

dim objTextFile
dim arrStr ' an array to hold the text content
dim sLine  ' holding text to write to new file

'Looping through all dropped file
For t = 0 to objArgs.Count - 1
    ' Input Path
    inPath = objFso.GetFile(wscript.arguments.item(t))

    ' OutPut Path
    outPath = replace(inPath, objFso.GetFileName(inPath), left(objFso.GetFileName(inPath), InStrRev(objFso.GetFileName(inPath),".") - 1) & "_SPACE_ADDED.csv")

    ' Read the file
    set objTextFile = objFso.OpenTextFile(inPath)


    'Now Creating the file can overwrite exiting file
    set aNewFile = objFso.CreateTextFile(outPath, True) 
    aNewFile.Close  

    'Open the file to appending data
    set aNewFile = objFso.OpenTextFile(outPath, 8) '2=Open for writing 8 for appending

    ' Reading data and writing it to new file
    Do while NOT objTextFile.AtEndOfStream
        arrStr = split(objTextFile.ReadLine,",")

        sLine = ""  'Clear previous data

        For i=lbound(arrStr) to ubound(arrStr)
            sLine = sLine + " " + arrStr(i) + ","
        Next

        'Writing data to new file
        aNewFile.WriteLine left(sLine, len(sLine)-1) 'Get rid of that extra comma from the loop


    Loop

    'Closing new file
    aNewFile.Close  

Next ' This is for next file

set aNewFile=nothing
set objFso = nothing
set objArgs = nothing

回复于 2024-04-27T08:21:56+08:00

0
我知道这是一个老问题，但问题不会很快消失 . CSV文件很容易从大多数编程语言中生成，相当小，人们可以在简单的文本编辑器中使用，并且无处不在 .

问题不仅在于文本字段中的 dates ，而且 anything numeric 也会从文本转换为数字 . 这是一个有问题的例子：
- 邮政编码
- 电话号码
- 政府身份证号码
有时 can 以一个或多个零（0）开始，当转换为数字时会被抛弃 . 或者该值包含可能与数学运算符混淆的字符（如日期： /， -） .

我能想到的两个案例是"prepending ="解决方案，如前所述， might not be ideal 是
- 文件可能导入MS Excel以外的程序（MS Word的邮件合并功能会出现在脑海中），
- 人类可读性可能很重要 .
我的黑客可以解决这个问题

如果在值中前置/附加非数字和/或非日期字符，则该值将被识别为文本而不进行转换 . 非打印字符会很好，因为它不会改变显示的值 . 但是，普通旧空格字符（\ s，ASCII 32）对此不起作用，因为它被Excel切掉，然后值仍然被转换 . But 还有各种其他打印和非打印空间字符，可以很好地工作 . The easiest 然而是 append (add after) the simple tab character (\t, ASCII 9).

这种方法的好处：
- 可通过键盘或易于记忆的ASCII码（9）获得，
- 不打扰输入，
- 通常不会打扰邮件合并结果（取决于模板布局 - 但通常只是在一行的末尾添加一个宽空格） . （如果这是一个问题，请查看其他字符，例如零宽度空间（ZWSP，Unicode U 200B）
  在记事本（等）中查看CSV时，
- 不是一个很大的障碍，
- 可以通过在Excel（或记事本等）中查找/替换来删除 .
- 您无需导入CSV，但只需双击即可在Excel中打开CSV .
如果您不想使用该选项卡，请在Unicode表中查找其他合适的选项 .

另一种选择

可能是生成XML文件，其中某些格式也被新的MS Excel版本接受导入，并且允许更多类似于.XLS格式的选项，但我没有这方面的经验 .

所以有各种选择 . 根据您的要求/应用，一个可能比另一个更好 .

加法

需要说明的是，MS Excel的新版本（2013）不会使Excel变得不那么有用......至少，存在解决它的说明 . 参见例如这个Stackoverflow：How to correctly display .csv files within Excel 2013? .
回复于 2024-04-27T08:21:56+08:00
3
我为此同样的问题所做的是在每个csv值之前添加以下内容：“=”“”和每个CSV值之后的一个双引号，然后在Excel中打开文件 . 以下列值为例：
```
012345,00198475
```
在Excel中打开之前应更改这些内容：
```
"="""012345","="""00198475"
```
执行此操作后，每个单元格值在Excel中显示为公式，因此不会格式化为数字，日期等 . 例如，值012345显示为：
```
="012345"
```
回复于 2024-04-27T08:21:56+08:00
0

将表粘贴到单词中 . 进行搜索/替换并将所有 - （破折号）更改为 - （双击） . 复制并粘贴到Excel中 . 可以对其他符号（/）等执行相同操作 . 如果需要在Excel中更改回一次破折号，只需将列格式化为文本，然后进行更改 . 希望这可以帮助 .

回复于 2024-04-27T08:21:56+08:00
4

如果在字段的开头放置一个倒置的逗号，它将被解释为文本 .

示例： 25/12/2008 变为 '25/12/2008

您还可以在导入时选择字段类型 .

回复于 2024-04-27T08:21:56+08:00
3
（EXCEL 2007及更高版本）

如何在不编辑源文件的情况下强制excel不“检测”日期格式

Either:
- 将文件重命名为.txt
- 如果您不能这样做，而不是直接在Excel中打开CSV文件，请创建一个新工作簿然后转到
  Data > Get external data > From Text
  并选择您的CSV .
无论哪种方式，您都会看到导入选项，只需选择包含日期的每列，并告诉excel将格式设置为“text”而不是“general” .
回复于 2024-04-27T08:21:56+08:00
13

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

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

在向导的第一页中，为文件类型选择“Delimited” .

在向导的第二页中，选择“，”作为分隔符，如果用引号括起值，也可以选择文本限定符

在第三页中，分别选择每一列，并为每个列分配“文本”而不是“常规”，以防止Excel弄乱您的数据 .

希望这可以帮助您或有类似问题的人！

回复于 2024-04-27T08:21:56+08:00
0
2018

唯一适合我的解决方案（也没有修改CSV） .

Excel 2010:
- 创建新工作簿
- 数据>从文本>选择您的CSV文件
- 在弹出窗口中，选择"Delimited"单选按钮，然后单击"Next >"
- 分隔符复选框：仅勾选"Comma"并取消选中其他选项，然后单击"Next >"
- 在"Data preview"中，滚动到最右侧，然后按住shift并单击最后一列（这将选择所有列） . 现在在"Column data format"中选择单选按钮"Text"，然后单击"Finish" .
Excel office365: （客户端版）
- 创建新工作簿
- 数据>从文本/ CSV>选择您的CSV文件
- 数据类型检测>不检测
Note: Excel office365（网络版），正如我写的那样，你将无法做到这一点 .
回复于 2024-04-27T08:21:56+08:00
27

我这样做的信用卡号码不断转换为科学记数法：我最终将我的.csv导入Google表格 . 导入选项现在允许禁用数值的自动格式化 . 我将任何敏感列设置为纯文本并下载为xlsx .

这是一个糟糕的工作流程，但至少我的 Value 观是他们应有的 .

回复于 2024-04-27T08:21:56+08:00
1

警告：Excel '07（至少）有一个（另一个）错误：如果字段内容中有逗号，它不会正确解析=“字段，内容”，而是将逗号后的所有内容放入以下字段，不管引号如何 .

我发现唯一的解决方法是在字段内容包含逗号时消除= .

这可能意味着有些字段无法在Excel中完全“正确”表示，但是现在我相信没有人会感到惊讶 .

回复于 2024-04-27T08:21:56+08:00
2

本周我遇到了这个约会，这似乎是一个很好的方法，但我无法在任何地方找到它 . 有人熟悉吗？你能引用它的来源吗？我没有看过几个小时，但我希望有人会认识到这种方法 .

示例1：=（"012345678905"）显示为 012345678905

示例2：=（"1954-12-12"）显示为 1954-12-12 ，而不是 12/12/1954 .

回复于 2024-04-27T08:21:56+08:00
8

这是我们在首先生成csv文件时使用的简单方法，它确实更改了一些值，因此它不适合所有应用程序：

在csv中为所有值预先添加空格

这个空间将被excel从诸如“1”，“2.3”和“-2.9e4”之类的数字中剥离出来，但将保留在“01/10/1993”之类的日期和“TRUE”之类的布尔值，阻止它们被转换为excel的内部数据类型 .

它还会在读入时停止双引号，因此在csv中制作文本的万无一失的方法仍然保持不变，即使是“3.1415”之类的文本是用双引号括起来并在整个字符串前加一个空格，即（使用单引号显示您要键入的内容）'“3.1415”' . 然后在excel中你总是有原始的字符串，除了它被双引号括起来并以空格为前缀，所以你需要考虑任何公式中的那些等 .

回复于 2024-04-27T08:21:56+08:00
95
（EXCEL 2016及更高版本，实际上我没有尝试过旧版本）
- 打开新的空白页面
- 转到标签"Data"
- 单击"From Text/CSV"并选择您的csv文件
- 检查预览您的数据是否正确 .
- 在某些列转换为日期时，单击"edit"，然后通过单击列 Headers 中的日历选择文本类型
- 点击"Close & Load"
回复于 2024-04-27T08:21:56+08:00
1
在不修改csv文件的情况下，您可以：
- 将Excel格式单元格选项更改为"text"
- 然后使用"Text Import Wizard"定义csv单元格 .
- 导入后删除该数据
- 然后只粘贴为纯文本
excel将正确格式化和分离您的csv单元格作为忽略自动日期格式的文本格式 .

这是一种愚蠢的工作，但它在导入之前会改变csv数据 . Andy Baird和Richard对这种方法有所不同，但错过了几个重要的步骤 .
回复于 2024-04-27T08:21:56+08:00
18

我发现在双引号之前加上'='可以实现你想要的 . 它强制数据为文本 .

例如 . =“2008-10-03”，=“更多文字”

EDIT (according to other posts) ：因为Excel 2007 bug noted by Jeffiekins应该使用solution proposed by Andrew： "=""2008-10-03"""

回复于 2024-04-27T08:21:56+08:00

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

30 回答

我的黑客可以解决这个问题

另一种选择

加法

如何在不编辑源文件的情况下强制excel不“检测”日期格式

相关问题