使用PowerShell将所有列都用双引号引起来导入和导出CSV

问题描述：

我正在使用以下代码导入和导出到CSV.我进行导出的原因是，我需要将导出的CSV中的所有列都用双引号引起来.但是在下面的代码中，双引号仅出现在每行的第一行和最后一行.

I am using below code to import and export to CSV. The reason why I am doing an export is because I need all columns in the exported CSV to be enclosed in double quotes. But with below code the double quote is only appearing at the first and last of every line.

Import-Csv c:\Emp.csv | Export-Csv c:\Emp1.csv -NoTypeInformation -Force

请注意，我已经尝试了以下代码(可以工作，但是如果CSV的大小> 200MB，则需要更长的时间):

Please note that I have already tried below code (that works but takes longer time if size of CSV is > 200MB):

$inform = Get-Content C:\A.csv 
$inform | % { 
  $info = $_.ToString().Replace("|", """|""") 
  $info += """" 
  $var = """" + $info 
  $var | Out-File D:\B.csv -Append 
}

样本输入(CSV文件):

Sample input (CSV file):


1|A|Test|ABC,PQR

样本输出(CSV文件):

Sample output (CSV file):


"1"|"A"|"Test"|"ABC,PQR"

答

Export-Csv已经单独在每个字段周围加上了双引号，因此您所需要做的就是指示它使用正确的定界符:

Export-Csv already adds double quotes around each field by itself, so all you need to do for that is instruct it to use the correct delimiter:

Import-Csv 'C:\path\to\input.csv' -Delimiter '|' |
    Export-Csv 'C:\path\to\output.csv' -Delimiter '|' -NoType

但是，Export-Csv会不加选择地在所有字段周围添加引号，因此，如果要有选择地在特定字段周围添加引号，则需要一个自定义例程:

However, Export-Csv adds quotes around all fields indiscriminately, so if you want to selectively add quotes around particular fields you're going to need a custom routine:

$reader = New-Object IO.StreamReader 'C:\path\to\input.csv'
$writer = New-Object IO.StreamWriter 'C:\path\to\output.csv'

while ($reader.Peek() -ge 0) {
    $line = $reader.ReadLine().Split('|')
    for ($i=0; $i -lt $line.Count; $i++) {
        if ($line[$i] -like '*,*') {
            $line[$i] = '"{0}"' -f $line[$i]
        }
    }
    $writer.WriteLine(($line -join '|'))
}

$reader.Close(); $reader.Dispose()
$writer.Close(); $writer.Dispose()

使用PowerShell将所有列都用双引号引起来导入和导出CSV

相关推荐