Database query output to CSV

Question

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?

Dane Boulton · Accepted Answer · 2015-08-11 17:33:04Z

2

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

answered Aug 11, 2015 at 17:33

Dane Boulton

1,3251 gold badge11 silver badges15 bronze badges

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

1 Comment

Roxx Over a year ago

Thanks for your answer. I will try tomorrow and let you know. Voted Up

Ansgar Wiechers · Accepted Answer · 2015-08-11 17:44:05Z

1

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

edited Aug 11, 2015 at 17:44

answered Aug 11, 2015 at 17:34

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

2 Comments

Roxx Over a year ago

Are you sure about that because most of things are out of my head.

Ansgar Wiechers Over a year ago

@404 I can't test this since I don't have access to an Oracle database, but I'd expect it to work. The approach is pretty much the same as the one suggested by Dane Boulton, except that it avoids appending to an array in a loop.

Collectives™ on Stack Overflow

Database query output to CSV

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related