Q: Database query output to CSV Below is my PowerShell code and it is working fine.
[Reflection.Assembly]::LoadFile("E:\oracle\product\11.2.0\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

$constr = "User Id=system;Password=pass;Data Source=API"
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$conn.Open()
$sql="select * from dba_users"
$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$reader=$command.ExecuteReader()

while($reader.Read()){
  $reader.GetString(0)
}

$conn.Close()

The problem is I want to export result to CSV. How to do this in PowerShell? Also, how can I show it in tabular format in PowerShell screen or output?

A: I have done something like this in the past I havent exported it as a csv but it should work.
$someArray = @()
    #read all rows into a hash table
    while ($reader.Read())
    {
        $row = @{}
        for ($i = 0; $i -lt $reader.FieldCount; $i++)
        {
            $row[$reader.GetName($i)] = $reader.GetValue($i)
        }
        #convert hashtable into an array of PSObjects
        $someArray += new-object psobject -property $row            
    }
$conn.Close()
$someArray | export-csv C:\temp\someFile.csv


A: Build custom objects from the fields of each record, then export the list of objects to a CSV:
$colNames = $reader.GetSchemaTable() | select -Expand ColumnName

$data = while ($reader.Read()) {
  $obj = New-Object -Type PSCustomObject

  for ($i = 0; $i -lt $colNames.Count; $i++) {
    $obj | Add-Member -Type NoteProperty -Name $colNames[$i] -Value $reader.GetString($i)
  }

  $obj
}

$data | Export-Csv 'C:\path\to\output.csv' -NoType

Code adopted from here (about halfway down the page, but you may want to read the article in its entirety).
Pipe the data into Format-Table to get tabular output in the PowerShell console:
$data | Format-Table -AutoSize

