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.
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.Hashtab le
IsReadOnly IsFixedSize IsSynchronized Keys Values SyncRoot Count
FALSE FALSE FALSE System.Collections.Hashtab le+KeyColl ection System.Collections.Hashtab le+ValueCo llection System.Object 1
PS command as below:
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/
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()
====================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.Hashtab
IsReadOnly IsFixedSize IsSynchronized Keys Values SyncRoot Count
FALSE FALSE FALSE System.Collections.Hashtab
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(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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.
Thank you once again for showing superb direction to resolve this issue.
ASKER
Perfect solution thank you foottech.
Thanks for the feedback.
ASKER
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.