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

excel csv import

465714 观看

30回复

0 作者的声誉

有谁碰巧知道是否可以为某个字段添加到我的csv中的令牌,因此Excel不会尝试将其转换为日期?

我正在尝试从应用程序写入.csv文件,并且其中一个值恰好看起来像日期,Excel会自动将其从文本转换为日期。我尝试将所有文​​本字段(包括看起来像日期的文本字段)放在双引号中,但这无效。

作者: user16324 的来源 发布者: 2008 年 10 月 2 日

回应 (30)


-1

128086 作者的声誉

如果在字段的开头放置逗号,则它将被解释为文本。

示例: 25/12/2008成为'25/12/2008

导入时,您还可以选择字段类型。

作者: Harley Holcombe 发布者: 02.10.2008 11:33

348

13585 作者的声誉

决定

我发现在双引号前加上'='可以实现您想要的功能。它将数据强制为文本。

例如。=“ 2008-10-03”,=“更多文字”

编辑(根据其他帖子):由于Jeffiekins指出Excel 2007错误,因此应使用Andrew建议解决方案"=""2008-10-03"""

作者: Jarod Elliott 发布者: 02.10.2008 11:33

8

10680 作者的声誉

(假设Excel 2003 ...)

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

作者: DocMax 发布者: 02.10.2008 11:34

-1

3627 作者的声誉

替代方法:

将要更改的列的格式转换为“文本”。选择要保留,复制的所有单元格。在不取消选择这些列的情况下,单击“编辑>选择性粘贴>作为值”

另存为CSV。请注意,这必须是您对文件所做的最后一件事,因为重新打开文件时,由于单元格格式无法保存在CSV文件中,因此它将自身设置为日期格式。

作者: Andy Baird 发布者: 02.10.2008 11:43

27

485 作者的声誉

警告:Excel '07(至少)有一个(另一个)错误:如果字段内容中有逗号,则不会正确解析=“ field,contents”,而是将逗号后的所有内容都放入后面的字段,而不考虑引号。

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

这可能意味着有些字段无法在Excel中准确表示“正确”,但是到目前为止,我相信没有人会感到惊讶。

作者: Jeffiekins 发布者: 10.12.2010 07:58

100

2410 作者的声誉

解决Jarod的解决方案以及Jeffiekins提出的问题后,您可以进行修改

"May 16, 2011"

"=""May 16, 2011"""
作者: Andrew Ferk 发布者: 16.05.2011 09:48

10

119 作者的声誉

这是我们首先在生成csv文件时在这里使用的简单方法,它确实会稍微改变值,因此并不适合所有应用程序:

在csv中的所有值前添加一个空格

excel会将此空格从数字中剥离,例如“ 1”,“ 2.3”和“ -2.9e4”,但日期仍保留为“ 01/011993”,布尔值保留为“ TRUE”,阻止它们转换为excel的内部数据类型。

它还会阻止双引号在读入时进行换行,因此excel即使在csv中使文本保持不变的一种万无一失的方法是,如果像“ 3.1415”这样的文本用双引号引起来并在整个字符串前加一个空格,即(使用单引号表示要输入的内容)““ 3.1415””。然后在excel中,您总是拥有原始字符串,除了它用双引号引起来并以空格开头,因此您需要在任何公式等中考虑它们。

作者: Dan 发布者: 28.01.2013 06:05

63

639 作者的声誉

我有一个类似的问题,这是无需编辑csv文件内容的解决方法,对我有帮助:

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

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

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

在第三页中,分别选择每个列,然后为每个列分配“文本”类型,而不是“常规”类型,以防止Excel弄乱您的数据。

希望这对您或有类似问题的人有所帮助!

作者: rainerbit 发布者: 21.02.2013 05:06

143

2259 作者的声誉

我知道这是一个老问题,但是问题不会很快消失。CSV文件很容易从大多数编程语言生成,相当小,使用纯文本编辑器可以在易于理解的情况下被人阅读,并且无处不在。

问题不仅在于文本字段中的日期,而且任何数字也将从文本转换为数字。有两个问题的示例:

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

有时可以以一个或多个零(0)开头,这些零在转换为数字时会被丢弃。或者该值包含可以与数学运算符混淆的字符(如日期:/,-)。

两种情况下,我能想到的是,“前面加上=”解决方案,如前面提到的,可能不是理想的

  • 文件可能会导入到MS Excel以外的程序中(想到了MS Word的邮件合并功能),
  • 易读性可能很重要的地方。

我的解决方法

如果在值的前面/后面加上一个非数字和/或非日期字符,则该值将被识别为文本,并且不会转换。非打印字符会很好,因为它不会更改显示的值。但是,普通的旧空格字符(\ s,ASCII 32)对此不起作用,因为它已被Excel截断,然后仍然转换了该值。但是,还有其他各种可打印的和非打印的空格字符也可以正常工作。但是,最简单的方法在简单制表符(\ t,ASCII 9)后面附加(添加)。

这种方法的好处:

  • 可通过键盘或易于记忆的ASCII码(9)获得,
  • 它不会打扰进口,
  • 通常不会打扰邮件合并结果(取决于模板布局-但通常只在行尾添加一个较大的空间)。(但是,如果这是一个问题,请查看其他字符,例如零宽度空格(ZWSP,Unicode U + 200B)
  • 在记事本(等)中查看CSV时不是很大的障碍,
  • 并且可以通过在Excel(或记事本等)中查找/替换来删除。
  • 您无需导入 CSV,而只需双击即可在Excel中打开 CSV。

如果由于某种原因您不想使用该选项卡,请在Unicode表中查找其他合适的内容。

另外一个选项

可能是生成XML文件,对于某些格式,较新的MS Excel版本也可以导入该格式的文件,并且允许使用更多类似于.XLS格式的选项,但是我对此没有经验。

因此,有多种选择。根据您的要求/应用,一个可能比另一个更好。


加成

应该说,新版本(2013+)的MS Excel不再以电子表格格式打开CSV-一个工作流中又出现了一次加速,这使得Excel的用处不大……至少,有解决此问题的说明。请参见例如以下Stackoverflow:如何在Excel 2013中正确显示.csv文件?

作者: fr13d 发布者: 27.02.2013 08:16

3

51 作者的声誉

我本周有一个强制性的约定,这似乎是一种很好的方法,但是我找不到任何地方引用它。有人熟悉吗?你能引用它的来源吗?我没有花几个小时,但希望有人会认识到这种方法。

示例1:=(“” 012345678905“)显示为012345678905

示例2:=(“” 1954-12-12“)显示为1954-12-12,而不是12/12/1954

作者: GW4 发布者: 13.06.2013 12:34

8

836 作者的声誉

这是我知道如何做到这一点而又不会弄乱文件本身的唯一方法。像往常一样,使用Excel,我在桌子上数小时不停地跳动来学习这一点。

将.csv文件扩展名更改为.txt;打开文件后,这将阻止Excel自动转换文件。我的操作方法是:将Excel打开到空白工作表,关闭空白表,然后单击File => Open并选择扩展名为.txt的文件。这将迫使Excel打开“文本导入向导”,在该向导中,您将询问有关如何解释文件的问题。首先,选择定界符(逗号,制表符等),然后(这是重要部分)选择一组设置的列,然后选择格式。如果您确切想要文件中包含的内容,则选择“文本”,Excel将显示分隔符之间的内容。

作者: Chris 发布者: 27.08.2013 03:08

15

159 作者的声誉

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

所有列都将作为文本导入,就像在CSV文件中一样。

作者: Rob Stockley 发布者: 06.02.2014 09:15

2

310 作者的声誉

嗨,我有同样的问题,

我编写此vbscipt来创建另一个CSV文件。新的CSV文件的每个字段的字体都会有一个空格,因此excel会将其理解为文本。

因此,使用下面的代码创建一个.vbs文件(例如Modify_CSV.vbs),然后保存并关闭它。原始文件拖放到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
作者: Harry Duong 发布者: 20.02.2014 06:37

20

2587 作者的声誉

在使用C#创建要写入CSV文件的字符串时,我必须采用以下方式对其进行格式化:

"=\"" + myVariable + "\""
作者: Colin Pear 发布者: 21.04.2014 01:09

0

1 作者的声誉

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

作者: Richard 发布者: 06.05.2014 01:14

2

29 作者的声誉

它不是Excel。Windows会识别公式,数据为日期并自动更正。您必须更改Windows设置。

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

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

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

作者: syandras 发布者: 08.05.2014 11:51

2

143 作者的声誉

无需修改csv文件,您可以:

  1. 将excel格式单元格选项更改为“文本”
  2. 然后使用“文本导入向导”定义csv单元。
  3. 导入后,删除该数据
  4. 然后将其粘贴为纯文本

excel将正确格式化和分隔您的csv单元格为文本格式,而忽略自动日期格式。

Kind of a silly work around, but it beats modifying the csv data before importing. Andy Baird and Richard sort of eluded to this method, but missed a couple important steps.

作者: cliffclof 发布者: 30.03.2015 07:00

1

23 作者的声誉

I know this is an old thread. For the ones like me, who still have this problem using office 2013 via powershell com object can use the opentext method. The problem is that this method has many arguments, that are sometimes mutual exclusive. To resolve this issue you can use the invoke-namedparameter method introduced in this post. An example would be

$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支持此功能,但是Microsoft的实现似乎有问题。同样,它也无法检测到德语特定的字符。提供正确的文化并不会改变这种行为。所有文件(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允许您使用矩阵设置一系列单元格的值。因此,我在此博客中使用了该算法来将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和底部的定界符即可。

作者: flatbeat 发布者: 17.04.2015 08:19

0

21 作者的声誉

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

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

我发现Google云端硬盘的Google表格不会将数字转换为日期。条形码每3个字符中确实有一个逗号,但是很容易删除。它可以很好地处理CSV,特别是在处理MAC / Windows CSV时。

有时可能会救人。

作者: EricNo7 发布者: 21.05.2015 10:45

0

1 作者的声誉

最简单的解决方案我今天才想到这一点。

  • 用Word打开
  • 将所有连字符替换为破折号
  • 保存并关闭
  • 在Excel中打开

完成编辑后,您随时可以再次在Word中将其打开以再次用连字符替换破折号。

作者: Randy Hoffman 发布者: 12.08.2015 03:10

4

312 作者的声誉

(EXCEL 2007及更高版本)

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

要么:

  • 将文件重命名为.txt
  • 如果您不能这样做,则可以直接在excel中创建一个新的工作簿,然后
    Data > Get external data > From Text
    选择CSV ,而不是直接在excel中打开CSV文件。

无论哪种方式,都将为您提供导入选项,只需选择每个包含日期的列,然后告诉excel将其格式设置为“文本”而不​​是“常规”。

作者: Some_Guy 发布者: 17.09.2015 09:39

12

179 作者的声誉

在Microsoft Office 2016版本中仍然是一个问题,对于那些使用基因名称(例如MARC1,MARCH1,SEPT1等)的人来说,这颇为困扰。在R中生成“ .csv”文件后,我发现该解决方案是最实用的,然后将其打开/与Excel用户共享:

  1. 以文本形式打开CSV文件(记事本)
  2. 复制它(ctrl + a,ctrl + c)。
  3. 将其粘贴到新的excel工作表中-它将全部粘贴为长文本字符串的一列。
  4. 选择/选择此列。
  5. 转到数据-“文本到列...”,在打开的窗口中选择“定界”(下一个)。检查是否已标记“逗号”(标记为已经将数据分隔到下面的列中)(下一个),在此窗口中,您可以选择所需的列并将其标记为文本(而不是常规)(完成)。

高温超导

作者: Ana Maria Mendes-Pereira 发布者: 30.11.2015 05:35

3

1215 作者的声誉

对于相同的问题,我要做的就是在每个csv值之前添加以下内容:“ =”“”,并在每个CSV值之后添加一个双引号,然后再在Excel中打开文件。以以下值为例:

012345,00198475

在Excel中打开之前,应将其更改为:

"="""012345","="""00198475"

执行完此操作后,每个单元格值都将在Excel中显示为公式,因此不会被格式化为数字,日期等。例如,值012345会显示为:

="012345"
作者: ChrisB 发布者: 08.12.2015 01:12

1

31 作者的声誉

在我的情况下,使用R生成的csv文件中的“ Sept8”已由Excel 2013转换为“ 8-Sept”。通过使用xlsx包中的write.xlsx2()函数来生成xlsx格式的输出文件,可以解决此问题。 ,可以由Excel加载而无需进行不必要的转换。因此,如果给定一个csv文件,则可以尝试使用write.xlsx2()函数将其加载到R中并将其转换为xlsx。

作者: Dinghai Zheng 发布者: 29.02.2016 02:43

0

29 作者的声誉

我这样做是为了使信用卡号不断转换为科学计数法:我最终将.csv导入到Google表格中。导入选项现在允许禁用数值的自动格式设置。我将所有敏感列设置为“纯文本”并下载为xlsx。

这是一个糟糕的工作流程,但至少我的价值观没有改变。

作者: Brendonwbrown 发布者: 16.08.2016 08:09

0

130 作者的声誉

我制作了这个vba宏,该宏在粘贴数字之前基本上将输出范围格式化为文本。当我想粘贴8 / 11、23 / 6、1 / 3等值而不用Excel将其解释为日期时,它对我来说非常理想。

Sub PasteAsText()
' Created by Lars-Erik Sørbotten, 2017-09-17
Call CreateSheetBackup

Columns(ActiveCell.Column).NumberFormat = "@"

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

ActiveCell.PasteSpecial

End Sub

我非常想知道这是否也适用于其他人。我一直在寻找解决这个问题的方法,但是我没有看到快速的vba解决方案,在此之前它不包括在输入文本前插入'。此代码以原始形式保留数据。

作者: LarsS 发布者: 17.09.2017 04:04

4

138 作者的声誉

这里提供的解决方案都不是好的解决方案。它可能适用于个别情况,但前提是您要控制最终显示。以我的例子为例:我的工作产生了出售给零售的产品清单。这是CSV格式,包含零件代码,其中一些以零开头,由制造商设置(不受我们控制)。去掉前导零,您实际上可能会匹配其他产品。由于后端处理程序,零售客户想要CSV格式的列表,这也是我们无法控制的,并且每个客户都不一样,因此我们无法更改CSV文件的格式。没有前缀“ =”,也没有添加标签。原始CSV文件中的数据正确;当客户在Excel中打开这些文件时,问题就开始了。而且许多客户并不是很精通计算机。他们几乎可以打开并保存电子邮件附件。我们正在考虑以两种略有不同的格式提供数据:一种为Excel Friendly(使用上面建议的选项,通过添加TAB,另一种为“主”格式)。但这可能是一厢情愿的想法,因为某些客户将不明白为什么我们需要这样做。与此同时,我们继续解释为什么他们有时会在电子表格中看到“错误的”数据。在Microsoft做出适当更改之前,只要没有人控制最终用户的方式,我就看不到任何适当的解决方案。使用文件。但这可能是一厢情愿的想法,因为有些客户将不明白为什么我们需要这样做。同时,我们继续解释为什么他们有时会在电子表格中看到“错误”数据。在Microsoft做出适当更改之前,只要没有人控制最终用户如何使用这些文件,我就不会对此找到合适的解决方案。但这可能是一厢情愿的想法,因为有些客户将不明白为什么我们需要这样做。同时,我们继续解释为什么他们有时会在电子表格中看到“错误”数据。在Microsoft做出适当更改之前,只要没有人控制最终用户如何使用这些文件,我就不会对此找到合适的解决方案。

作者: mljm 发布者: 30.11.2017 10:59

1

2330 作者的声誉

使用Google云端硬盘(如果是Mac,则为Numbers)的解决方法:

  1. 在Excel中打开数据
  2. 将数据错误的列的格式设置为“文本”(“格式”>“单元格”>“数字”>“文本”)
  3. 将.csv加载到Google云端硬盘中,然后使用Google表格将其打开
  4. 复制问题列
  5. 将列作为文本粘贴到Excel中(编辑>选择性粘贴>文本)

或者,如果您在Mac上执行步骤3,则可以在“数字”中打开数据。

作者: Derek Hill 发布者: 19.12.2017 10:00

1

845 作者的声誉

(EXCEL 2016及更高版本,实际上我没有在旧版本中尝试过)

  1. 打开新的空白页
  2. 转到标签“数据”
  3. 点击“来自文本/ CSV”,然后选择您的csv文件
  4. 在预览中检查您的数据是否正确。
  5. 在某些列转换为日期的情况下,单击“编辑”,然后通过单击列标题中的日历来选择文本类型
  6. 点击“关闭并加载”
作者: Roman Yakoviv 发布者: 27.06.2018 09:45

11

9354 作者的声誉

2018年

唯一适用于我的解决方案(也无需修改CSV)。

Excel 2010:

  1. 创建新工作簿
  2. 数据>从文本>选择CSV文件
  3. 在弹出窗口中,选择“定界”单选按钮,然后单击“下一步>”。
  4. 分隔符复选框:仅勾选“逗号”,然后取消选中其他选项,然后单击“下一步>”
  5. 在“数据预览”中,滚动到最右边,然后按住shift并单击最后一列(这将选择所有列)。现在,在“列数据格式”中,选择单选按钮“文本”,然后单击“完成”。

Excel office365 :(客户端版本)

  1. 创建新工作簿
  2. 数据>来自文本/ CSV>选择CSV文件
  3. 数据类型检测>不检测

注意:在撰写本文时,Excel office365(网络版)将无法执行此操作。

作者: evilReiko 发布者: 12.08.2018 07:59
32x32