Unable to get database to write to csv in powershell

Question

I have powershell that reads from a database and successfully outputs. Problem is, when I insert commas, the program ignores it so when I open .csv file, all the data is in one column instead of five columns. How to fix this?

$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" 
}

TheMadTechnician · Accepted Answer · 2014-07-17 18:33:23Z

3

I would suggest creating an array of objects instead of an array of strings, and then you can just use Export-CSV. It would go something like:

    $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
    }

Then for output:

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

answered Jul 17, 2014 at 18:33

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amrit · Accepted Answer · 2014-07-17 18:20:02Z

1

 $sql_output += @($rdr.GetValue(0)+ """,""" +$rdr.GetValue(1)+ """,""" +$rdr.GetValue(2)+ """,""" +$rdr.GetValue(3)+ """,""" +$rdr.GetValue(4))

answered Jul 17, 2014 at 18:20

Amrit

4216 silver badges23 bronze badges

1 Comment

Glowie Over a year ago

when I open the file it says error, and it's still not separated into different columns

Collectives™ on Stack Overflow

Unable to get database to write to csv in powershell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related