如何将Excel（xlsm）保存为分号分隔的CSV？PowerShell实现遇阻

嘿，我懂你这会儿的头疼——前面打开Excel、刷新数据、保存原文件都顺顺利利完成了，结果卡在CSV分隔符这儿，PowerShell默认用逗号，但SQL Server那边需要分号对吧？别慌，咱们来把这个问题解决掉。

下面给你两种靠谱的解决方案，你可以根据自己的需求选：

方法一：借助Excel的SaveAs方法，强制用分号分隔 #

这种方法直接用你已经创建好的Excel COM对象，通过临时修改会话区域设置，让Excel保存CSV时用分号作为分隔符：

# 临时克隆当前区域设置，避免修改系统全局设置
$currentCulture = [System.Globalization.CultureInfo]::CurrentCulture.Clone()
# 将列表分隔符改为分号
$currentCulture.TextInfo.ListSeparator = ";"
# 应用到当前PowerShell会话
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture

# 定义CSV保存路径
$csvPath = "C:\test\test.csv"
# 用Excel的SaveAs保存为CSV，Local参数确保使用我们修改后的区域设置
$wb.SaveAs($csvPath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV, Local:= $true)

方法二：读取Excel数据到PowerShell对象，再导出指定分号 #

这种方法不依赖Excel的区域设置，完全由PowerShell控制导出逻辑，灵活性更高，适合需要对数据做额外处理的场景：

$csvPath = "C:\test\test.csv"
# 获取目标工作表（这里用第一个工作表，你可以改成实际的表名，比如$wb.Worksheets["你的表名"]）
$ws = $wb.Worksheets.Item(1)
# 获取工作表中已使用的单元格范围
$usedRange = $ws.UsedRange
# 读取范围内的所有数据到数组
$values = $usedRange.Value2

# 提取表头（第一行数据）
$headers = $values[0,0..($usedRange.Columns.Count-1)]
# 将每行数据转换成PowerShell自定义对象
$rows = for ($i=1; $i -lt $usedRange.Rows.Count; $i++) {
    $row = [ordered]@{}
    for ($j=0; $j -lt $usedRange.Columns.Count; $j++) {
        $row[$headers[$j]] = $values[$i,$j]
    }
    [PSCustomObject]$row
}

# 导出为分号分隔的CSV，指定编码避免乱码
$rows | Export-Csv -Path $csvPath -Delimiter ";" -NoTypeInformation -Encoding UTF8

完整整合后的代码 #

把上面的方法整合到你现有的脚本里，别忘了清理Excel进程，避免残留后台进程：

# Refresh Excel
$app = New-Object -comobject Excel.Application
$app.Visible = $false
$wb = $app.Workbooks.Open("C:\test\test.xlsm")

# 刷新所有数据连接
$wb.RefreshAll()
# 等待所有异步刷新完成（避免未刷新完就保存）
foreach ($conn in $wb.Connections) {
    if ($conn.OLEDBConnection -and $conn.OLEDBConnection.Refreshing) {
        while ($conn.OLEDBConnection.Refreshing) {
            Start-Sleep -Milliseconds 500
        }
    }
}

# 保存原Excel文件
$wb.Save()

# --- 转换为分号分隔的CSV ---
$csvPath = "C:\test\test.csv"

# 选择其中一种方法即可，这里注释掉另一种
# 方法一：Excel SaveAs + 区域设置
$currentCulture = [System.Globalization.CultureInfo]::CurrentCulture.Clone()
$currentCulture.TextInfo.ListSeparator = ";"
[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture
$wb.SaveAs($csvPath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV, Local:= $true)

# 方法二：PowerShell读取导出
<#
$ws = $wb.Worksheets.Item(1)
$usedRange = $ws.UsedRange
$values = $usedRange.Value2

$headers = $values[0,0..($usedRange.Columns.Count-1)]
$rows = for ($i=1; $i -lt $usedRange.Rows.Count; $i++) {
    $row = [ordered]@{}
    for ($j=0; $j -lt $usedRange.Columns.Count; $j++) {
        $row[$headers[$j]] = $values[$i,$j]
    }
    [PSCustomObject]$row
}

$rows | Export-Csv -Path $csvPath -Delimiter ";" -NoTypeInformation -Encoding UTF8
#>

# 清理Excel COM对象，避免后台残留进程
$wb.Close($false)
$app.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ws) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($app) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

一些注意事项 #

• 方法一的区域设置修改仅对当前PowerShell会话有效，不会改动系统全局设置，放心用。
• 如果你的Excel数据里包含逗号，用分号分隔会避免SQL Server导入时出现列错位的问题。
• 记得添加刷新等待逻辑，不然可能会出现CSV里是未刷新的旧数据。

内容的提问来源于stack exchange，提问作者Giuseppe Lolli