CODEWITHSUNDEEP
oracle-databasepowershell
jleusjr
jleusjr

Reputation: 3

Oracle query and output to CSV

I would like to seek for advice on how am I going to output the oracle query to csv with same wide format output from oracle query. below is the excerpt from my code 

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null 
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString) 
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection) 
$connection.Open() 
$users = $command.ExecuteReader() 
$Counter = $users.FieldCount 

$dataset = 
  @(  while ($users.Read()) { 
         for ($i = 0; $i -lt $Counter; $i++) { 
       New-Object PsObject -Property @{ Key = $users.GetName($i)
                                       Value = $users.GetValue($i)
                                     }
                                     }
    }) 
$connection.Close()

Output:

PS C:\WINDOWS\system32> $dataset

Key             Value          
---             -----          
PRCDNAME        A123rw-T03 
ASSY_PART1      wrwrwee-A03  
ASSY_PART2      wrewrwe-A0CAD
ASSY_PART3      wer         
ASSY_PART4                     
ASSY_PART5                     
ASSY_PART6                     
OUTPN_1         rgsrggs-3     
OUTPN_2                        
OUTPN_3                        
OUTPN_4                        
OUTPN_5                        
OUTPN_6                        
OUTPN_7                        
OUTPN_1_ALLOC1

I want to format it as below. basically I need the Key column as the header

PRCDNAME,ASSY_PART1,ASSY_PART2 ,......,

A123rw-T03,wrwrwee-A03,wrewrwe-A0CAD,....,

Upvotes: 0

Views: 1114

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Create a hashtable from your object list:

$ht = @{}
$dataset | ForEach-Object { $ht[$_.Key] = $_.Value }

Then make an object from that hashtable and export it to a CSV:

New-Object -Type PSObject -Property $ht | Export-Csv 'C:\path\to\output.csv' -NoType

Upvotes: 1

Related Questions