sql - 无法使数据库在Powershell中写入CSV

Question

我有从数据库读取并成功输出的Powershell。问题是，当我插入逗号时，程序将忽略它，因此当我打开.csv文件时，所有数据都位于一列而不是五列中。如何解决这个问题？

$count=0
do{
    try{
        $rdr = $cmd.ExecuteReader()


        while ($rdr.read()){
            $sql_output += ,@($rdr.GetValue(0), ",", $rdr.GetValue(1), ",", $rdr.GetValue(2), ",", $rdr.GetValue(3), ",", $rdr.GetValue(4))
            $count=$count + 1
        }
        $transactionComplete = $true
    }
    catch{
        $transactionComplete = $false
    }
}until ($transactionComplete)

$conn.Close()

foreach ($k in $sql_output){
     Add-Content D:\Script\Network_Threat_Protection.csv "$k" 
}

Answer 1

我建议创建一个对象数组而不是字符串数组，然后可以只使用Export-CSV。它会像这样:

    $sql_output = @()
    while ($rdr.read()){
        $sql_output += [PSCustomObject][Ordered]@{
            Col1=$rdr.GetValue(0)
            Col2=$rdr.GetValue(1)
            Col3=$rdr.GetValue(2)
            Col4=$rdr.GetValue(3)
            Col5=$rdr.GetValue(4)
        }
        $count=$count + 1
    }

然后输出:

$sql_output | Export-CSV "D:\Script\Network_Threat_Protection.csv" -NoTypeInfo -Append