In Powershell, I want to export the results of a query ODP.NET to a CSV file
This is my code:
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
$connection.open()
$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand -ArgumentList $query
$cmd.Connection = $connection
$reader = $cmd.ExecuteReader()
This code is working with the correct values of $connectionString and $query variables. If I add this code I can read the correct result of my query:
while ($reader.Read()) {
$col1 = $reader["Col1"]
$col2 = $reader["Col2"]
$col3 = $reader["Col3"]
#Write-Host $col1, $col2, $col3
}
Those 3 columns are an example, in my real case I have many more. Then I want to export directly to a CSV file, something like this:
XXXXXXX | export-csv -Delimiter ";" -Path "E:\export.csv"
How can I do this? The expected result is a CSV file similar than this:
"4581";"6";26867;"191057";"BH02 - 26867 - ";"30/05/2019";"";"2040";1991,04;"2040";2,4;"00";"";348;"";"1";"1";"";"";"BRL";2040;"";1
"4581";"4";28313;"747990";"BH02 - 28313 - ";"30/05/2019";"";"140";137,13;"140";2,05;"00";"";459;"";"1";"1";"";"";"BRL";140;"";1
"4581";"1";28316;"881411";"BH02 - 28316 - ";"30/05/2019";"";"140";137,13;"140";2,05;"00";"";460;"";"1";"1";"";"";"BRL";140;"";1
"4581";"1";;"878676";"BH02 - - 275650/PF19";"28/05/2019";"";"103";100,8885;"103";2,05;"00";"";305;"";"1";"1";"";"";"BRL";103;"";1
"4581";"6";28168;"006778";"BH02 - 28168 - 275714/PF19";"30/05/2019";"";"848";828,92;"848";2,25;"00";"";429;"";"1";"1";"";"";"BRL";848;"";1
"4581";"3";29080;"641559";"BH02 - 29080 - ";"30/05/2019";"";"3424,14";3338,5365;"3424,14";2,5;"00";"";488;"";"1";"1";"";"";"BRL";3424,14;"";1
"4581";"4";;"602483";"BH02 - - 23443";"28/05/2019";"";"157";153,7815;"157";2,05;"00";"";329;"";"1";"1";"";"";"BRL";157;"";1
What is the raw output of this Oracle command?
If it is a list, then use -Join and use the Csv cmdlets to export to convert to a csv file.
This also would indicate that you are new or never used PowerShell before since csv use case is a daily thing. That's OK, but jump on YouTube, MSDN Channel9 and go through the videos on PowerShell, beginning/intermediate/advanced, PowerShell and Databases, PowerShell and Csv files and get ramped up to limit/avoid, misconceptions, frustrations, bad habits, etc.
It should be straightforward as doing something like this ( of course don't put passwords in scripts)...
Add-Type -Path 'C:\oracle\instantclient_10_2\odp.net\managed\common\Oracle.ManagedDataAccess.dll'
$username = "USERID"
$password = "Password"
$datasource = "HOST:PORT/Instance"
$connectionString = "User Id = $username;Password = $password;Data Source = $datasource"
$query = "SELECT DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA7, DATA8
FROM TABLE WHERE NOT REGEXP_LIKE (EMAIL_ID, '@domain.com','i') order by DATA2"
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("$connectionString")
$connection.open()
$command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
$command.Connection = $connection
$command.CommandText = $query
$ds = New-Object system.Data.DataSet
$da = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($command)
[void]$da.fill($ds)
return $ds.Tables[0] |
SELECT DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA7, DATA8 |
Export-CSV "C:\test.csv" -NoTypeInformation
$connection.Close()