根据Jarod的解决方案和Jeffiekins提出的问题，你可以进行修改

"May 16, 2011"

to

"=""May 16, 2011"""

我知道这是个老话题了。对于像我这样的人来说，通过PowerShell COM对象使用Office 2013仍然有这个问题，可以使用opentext方法。问题是这个方法有很多参数，有时是相互排斥的。要解决这个问题，您可以使用本文介绍的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支持的，但微软的实现似乎有bug。 此外，它不知何故没有检测到德语特有的字符。给它正确的文化并没有改变这种行为。所有文件(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" ";"

你可以按原样使用上面的代码;它应该将任何CSV转换为Excel。只需将路径更改为CSV和底部的分隔符。

我对不断转换为科学符号的信用卡号这样做:我最终将.csv导入到谷歌Sheets中。导入选项现在允许禁用数字值的自动格式化。我将任何敏感列设置为纯文本并下载为xlsx。

这是一个糟糕的工作流程，但至少我的价值观保持了原样。

None of the solutions offered here is a good solution. It may work for individual cases, but only if you're in control of the final display. Take my example: my work produces list of products they sell to retail. This is in CSV format and contain part-codes, some of them start with zero's, set by manufacturers (not under our control). Take away the leading zeroes and you may actually match another product. Retail customers want the list in CSV format because of back-end processing programs, that are also out of our control and different per customer, so we cannot change the format of the CSV files. No prefixed'=', nor added tabs. The data in the raw CSV files is correct; it's when customers open those files in Excel the problems start. And many customers are not really computer savvy. They can just about open and save an email attachment. We are thinking of providing the data in two slightly different formats: one as Excel Friendly (using the options suggested above by adding a TAB, the other one as the 'master'. But this may be wishful thinking as some customers will not understand why we need to do this. Meanwhile we continue to keep explaining why they sometimes see 'wrong' data in their spreadsheets. Until Microsoft makes a proper change I see no proper resolution to this, as long as one has no control over how end-users use the files.

如果有人还在寻找答案，下面这句话对我来说非常合适

我输入=("my_value")。 即=("04SEP2009")显示为04SEP2009，而不是09/04/2009

这同样适用于大于15位的整数。他们不再修剪了。

在微软Office 2016版本中，这仍然是一个问题，这让我们这些研究基因名称的人感到不安，如MARC1、MARCH1、SEPT1等。 我发现最实用的解决方案是在R中生成一个“。csv”文件，然后将与Excel用户打开/共享:

以文本(记事本)形式打开CSV文件 复制它(ctrl+a, ctrl+c)。 粘贴到一个新的excel表格中-它将全部粘贴在一列作为长文本字符串。 选择/选择此列。 转到Data-“Text to columns…”，在打开的窗口中选择“delimited”(下一步)。检查“逗号”被标记(标记它将显示数据与下面列的分离)(下一步)，在这个窗口中，您可以选择您想要的列并将其标记为文本(而不是通用)(完成)。

HTH