Powershell and Oracle, exporting SQL data to csv

Question

I have an Oracle SQL table that contains email addresses. I need to extract this data and insert it into an array, and export to a csv file. I'm not too sure where to go from here. My code so far is:

$odpAssemblyName = "Oracle.DataAccess, Version=2.112.3.0,
Culture=neutral, PublicKeyToken=89b483f429c47342"
[System.Reflection.Assembly]::Load($odpAssemblyName)

$con = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=HR;Password=oracle;Data Source=XE")
$cmd=$con.CreateCommand()
$cmd.CommandText= @"

SELECT DISTINCT email FROM 
hr.emails WHERE acct_enabled = 'Y' 
AND UPPER(email) NOT LIKE 'AIS%'ORDER BY email
"@

$con.Open()

$rdr=$cmd.ExecuteReader()
$columnNames=$rdr.GetSchemaTable() | Select-Object -ExpandProperty ColumnName

$resultSet=@()

while ($rdr.Read()) {

    $intData=$rdr.GetOracleString(0)

    "{0,0}" -f $intData.Value

}

$con.Close()

Can anybody help me with my While loop (I'm new to programming mostly) and help me add the result set to an array, and export the result set to a nice little csv file?

Thanks in advance for any help/pointers

James

I have declared a blank array $resultSet=@() in preparation for what I think needs to happen! — user4691770
– user4691770, Commented Jul 22, 2015 at 19:28
Silly question - you're only selecting email, right? This CSV file will just be a list of emails, each email on its own line, with no commas at all, right? Just curious why you mention CSV... this will look like a text file — Patrick87
– Patrick87, Commented Jul 22, 2015 at 19:30
Hi Patrick, no not a silly question it's a good point :) The Array is what I really want to do with it. The CSV file would be an added bonus. There are two ways of doing this, I could add the commas in my SQL statement or add them with PowerShell, I would like to know how to do it with PowerShell. Failing that a simple non-delimited txt file would also be fine. My goal is to use then create a dynamic distribution group on an Exchange server and have PowerShell populate it, but one step at a time... — user4691770
– user4691770, Commented Jul 22, 2015 at 20:27

ATek · Accepted Answer · 2015-07-23 04:38:35Z

0

Try something like this, although I cant exactly test it based on no oracle DB available on my side.

$odpAssemblyName = "Oracle.DataAccess, Version=2.112.3.0,
Culture=neutral, PublicKeyToken=89b483f429c47342"
[System.Reflection.Assembly]::Load($odpAssemblyName)

$con = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=HR;Password=oracle;Data Source=XE")
$cmd=$con.CreateCommand()
$cmd.CommandText= @"

SELECT DISTINCT email FROM 
hr.emails WHERE acct_enabled = 'Y' 
AND UPPER(email) NOT LIKE 'AIS%'ORDER BY email
"@

$con.Open()

 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SqlAdapter.SelectCommand = $cmd
 $DataSet = New-Object System.Data.DataSet
 $SqlAdapter.Fill($DataSet)

$emaillist = $DataSet.Tables[0].Email



$con.Close()

$emaillist | Export-csv "C:\Somefolder\OracleEmails.csv" -NoTypeInformation

edited Jul 23, 2015 at 4:38

answered Jul 22, 2015 at 22:14

ATek

8252 gold badges8 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Powershell and Oracle, exporting SQL data 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