Oracle query and output to CSV

Question

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,....,

Ansgar Wiechers · Accepted Answer · 2017-03-08 16:45:10Z

1

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

answered Mar 8, 2017 at 16:45

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

Collectives™ on Stack Overflow

Oracle query and output to CSV

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related