导入带有公式的 .csv 文件

Question

我使用PowerShell导入了一个逗号分隔的CSV文件。它被成功导入并且看起来正常。问题是，单元格包含公式，例如=20+50+70。除非我点击顶部字段中的回车键，否则不会计算。另一个问题是，有些单元格包含类似于=50,2+70,5这样的数字。Excel根本无法理解它们。除非我删除逗号或将其替换为点（.），否则它无法计算。但这不是一个可行的解决方案。如何解决这个问题？使用以下PowerShell命令导入CSV文件：

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$wbpath=Join-Path "$psscriptroot" 'file.xlsx'
$importcsv=Join-Path "$psscriptroot" 'file.csv'
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.Workbooks.OpenText($importcsv)
$xl.DisplayAlerts = $false
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$xl.ActiveWorkbook.SaveAs($wbpath,51)
$xl.Quit()
while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){'released'}

The

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

如果我的系统语言环境不是美国英语，那么这个操作是必须的，否则会出错。

谢谢。

CSV 样例：

name1.name1.name1,"=20","=7,65","=20,01"
name2.name2.name2,"=20+10","=4,96+0,65","=20,01+10"
name3.name3.name3,"=20","=4,96+0,88","=21,01+11"

- user3019059

---

CSV文件不包含公式，所以您的意思是某些单元格包含这些字符串吗？我不确定“除非我在顶部字段中点击Enter”是什么意思。您是否有一些示例输入和预期输出？这将使确定更容易。 - Matt

CSV文件看起来像这样：Name，=20+20，=20，=1+80，5+9，=20等等。Excel应该解释并计算它。就像我手动导入CSV或手动输入数据一样。顶部字段是指Excel中的编辑字段，在顶部。 - user3019059

谢谢。我更新了问题。 - user3019059

我也遇到了同样的问题。在我的情况下，我最初使用Powershell直接创建Excel文件。然而，这样做比使用Excel对象慢了50多倍。当使用Excel对象时，处理800条记录需要10分钟。使用CSV对象只需要几秒钟。我发现，在Powershell中使用Excel对象创建超链接是延迟的主要原因。我的解决方法是创建CSV，将字段设置为类似于“Z = hyperlink（“url”，“text”）”，然后在Excel中进行查找/替换，“Z =”替换为“=”，每次更新数据都需要执行此操作。 - ProgMasta

你可能想尝试将小数分隔符设置为“，”（Get-Culture）。NumberFormat.NumberDecimalSeparator = '，' - Mani Live

Answer 1

听起来你需要：

a) 强制工作表进行计算

b) 如果你要坚持使用en-US语言环境，那么你需要将那些逗号替换为小数点。这是GB/US标准以及Excel解释小数的方式。但我强烈建议你坚持使用数据设置的语言环境。

（由于我目前在使用Mac电脑，因此未经测试）

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$wbpath=Join-Path "$psscriptroot" 'file.xlsx'
$importcsv=Join-Path "$psscriptroot" 'file.csv'
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$wb = $xl.Workbooks.OpenText($importcsv)
$xl.DisplayAlerts = $false
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$sh = $wb.Sheets.Item(1)
# loop through the used range and replace any commas with decimals
foreach ($cell in $sh.usedRange)
{
    [string]$formula = $cell.formula
    $formula -replace ',','.'
    $cell.formula = $formula
}
# force the sheet to calculate
$sh.Calculate()
$xl.ActiveWorkbook.SaveAs($wbpath,51)
$xl.Quit()
while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){'released'}

- SierraOscar

Answer 2

与前面的答案一样，您必须考虑本地化；根据编码国家/地区不同，不是所有的.csv文件都具有相同的格式。虽然UTF是标准，但在某些方面，CSV是一种“遗留格式”，即使它是使用纯文本传输数据的最轻量级、简单的方式。

Sam已经回答了大部分困难的问题，所以我只想补充一些东西。如果您正在制作自动化解决方案并且需要处理多个国家/地区，有几种方法可以确定它的编码方式。您可以采用更技术熟练的路线，并实现类似于此https://gist.github.com/jpoehls/2406504的自定义函数，或者因为它是CSV，您可以做出一个不错的猜测，因为最常见的编码格式使用不同的分隔符；我相信您提到的那个使用制表符作为编码。

我将专注于Excel导入中未提及的部分。在“数据”选项卡中有一个相当不错的功能，可以根据使用的分隔符自定义导入。在第三步中，当您按高级选项时，它允许您告诉它源数据使用的分隔符（逗号或小数点），一旦您选择并按完成，它将将结果转换为Excel所设置的区域设置，并正确评估函数。示例图片 因此，这个工作流程是打开一个新的Excel文档，选择“数据”>“从文本”并从那里继续。它将把您选择的区域设置（在您的情况下可能是1252）的文本转换为您指定的任何小数格式。

- Metalsand