Link to home
Start Free TrialLog in
Avatar of ipsec600
ipsec600

asked on

Export Oracle view data in CSV format via PS

I am able to read Oracle view data via Power Shell, but need to export that data in CSV format, can you please advise.
I am using the following code to read the data from oracle view.
$connectionString = "Data Source=db.bl.com;User Id=oracleuser;Password=P@ssW0rd;Integrated Security=no" 
$queryString =     "SELECT * FROM intranet_owner.view_AA_6E"

[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

while ($users.Read()) { 
     for ($i = 0; $i -lt $Counter; $i++) { 
          @{ $users.GetName($i) = $users.GetValue($i); } 
    } 
}

$connection.Close()

Open in new window

====================
While I tried to use the following PS command, it exported CSV file but not returning actual data rather returning the following output in the CSV file :

#TYPE System.Collections.Hashtable                                    
IsReadOnly      IsFixedSize      IsSynchronized      Keys      Values      SyncRoot      Count
FALSE      FALSE      FALSE      System.Collections.Hashtable+KeyCollection      System.Collections.Hashtable+ValueCollection      System.Object      1

PS command as below:

$connectionString = "Data Source=db.bl.com;User Id=oracleuser;Password=P@ssW0rd;Integrated Security=no" 
$queryString =     "SELECT * FROM intranet_owner.view_AA_6E"

[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

while ($users.Read()) { 
     for ($i = 0; $i -lt $Counter; $i++) { 
          @{ $users.GetName($i) = $users.GetValue($i); } | export-csv C:\Users\bluser01\Desktop\export.csv
    } 
}

$connection.Close(

Open in new window


Reference link followed:
http://www.gunnalag.com/2013/12/05/powershell-connecting-and-querying-oracle-database-contents-from-a-windows-7-x64-system-display-results-row-by-row/
ASKER CERTIFIED SOLUTION
Avatar of footech
footech
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ipsec600
ipsec600

ASKER

Hello footech, it just Awesome, thanks a lot for your excellent support. The script works perfectly and I am getting all the data in the CSV format, I have used the second query that you have provided, it works like magic.

Could please advise me for one more query, I am noticing in the CSV file, data are coming only under Key, and Value filed, can you please advice how can  I export data
in the table format

EMPLOYEE_ID      FIRST_NAME      LAST_NAME      EMAIL_ADDRESS START_DATE       TITLE
11                      test            user                   testuser@bl.com 10/10/2009    Contractor  
 
At present it is coming as below:

Key             Value
EMPLOYEE_ID      11
FIRST_NAME       test
LAST_NAME       user
EMAIL_ADDRESS    testuser@bl.com
START_DATE       10/10/2009
TITLE             Contractor
GROUP_NAME      IT

Thanks once again for your excellent support.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Lastly, if you're using PS 3.0 the following might work (and be faster).  It was pulled from ideas at http://blogs.technet.com/b/heyscriptingguy/archive/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access.aspx
$columnNames=$users.GetSchemaTable() | Select-Object -ExpandProperty ColumnName
@(while ($users.Read()) {
    $result=[ordered]@{} 
    for ($i=0; $i -lt $Counter; $i++) {
        $result.Add($columnNames[$i], $users.GetOracleValue($i))
    }
    [pscustomobject]$result
}) | export-csv C:\Users\bluser01\Desktop\export.csv -notype

Open in new window

Thanks a lot footech for the excellent resolution, the first command perfectly works for me, it simply awesome. I am getting my expected output. I have few more query for which I will ask another question.

Thank you once again for showing superb direction to resolve this issue.
Perfect solution thank you foottech.
Thanks for the feedback.