PS script to get data from Oracle DB and export to CSV

Welcome Forums General PowerShell Q&A PS script to get data from Oracle DB and export to CSV

Viewing 9 reply threads
  • Author
    Posts
    • #56020
      Participant
      Topics: 16
      Replies: 32
      Points: 80
      Rank: Member

      Hello again.

      I am working on a project that I need to automate the creation of a CSV file based on a SQL query to a oracle DB.
      I have written the SQL script to make the query I need and saved that off as a .sql file.
      I have also done my research and downloaded the ODP.NET_Managed_ODAC12cR4 and installed it. I have also tested the ability to load in the DLL to PS via PS C:\Windows\System32\WindowsPowerShell\v1.0> Add-Type -Path C:\oracle\instantclient_10_2\odp.net\managed\common\Oracle.ManagedDataAccess.dll.
      So now I just need to create the connection to the DB, execute the .sql script and export the results to a CSV.
      I am continuing to search the internet for a solution but I always find that someone here has the answer long before I find it out in the wild.

      As always Thanks for your assistance!!!!

    • #56095
      Participant
      Topics: 16
      Replies: 1792
      Points: 3,290
      Helping Hand
      Rank: Community Hero

      The Oracle Client is typically required because it installs the Oracle ODBC drivers that allow you to connect to Oracle. You can check ConnectionStrings.com to get examples of the strings you’ll need to make the connection. A search for Powershell Oracle turned up a lot of examples like this: http://guyharrison.typepad.com/oracleguy/2008/01/accessing-oracl.html

    • #56285
      Participant
      Topics: 16
      Replies: 32
      Points: 80
      Rank: Member

      Thanks for the links. It has been quite helpful.
      I have made a lot of progress but I am getting an error when I try to run the script.
      Here is the secipt

      And the error

      Now it seems like the CommandText is not being passed to the execute command but when I brake the code down and enter it one line at a time I can enter $command and I get this result

      As always thanks for your assistance!!!

    • #56348
      Participant
      Topics: 16
      Replies: 32
      Points: 80
      Rank: Member

      Ok. I was able to figure out what I was doing wrong above and now the script is executing and I am getting the data I want displayed in the console. Now I just need to get the data out to a csv file. I have been noodling with it for a while tonight and not making the progress I want.
      I was toying with using Set-Content but I am having trouble getting the data to be added to the file. And to make it more interesting I need to customize the column headers.
      here is my code that gets all the data I need. Just need help piping it out to a csv.

      Thanks in advance!!!

    • #56374
      Participant
      Topics: 16
      Replies: 1792
      Points: 3,290
      Helping Hand
      Rank: Community Hero

      Read this Scripting Guy blog, which talks in depth about how to connect to Oracle, performance, etc. as well as functions to get the data.

      Most of the reader() (Get-OracleResultRdr)solutions are generating a PSObject, which is what you want to do. The Get-OracleResultDa function fills a DataTable, so you will need to convert it to a PSObject. Regardless, of the function you choose, you should do something like this:

      The Select(-Object) will create a PSObject so that you can easily export it to a CSV.

    • #56588
      Participant
      Topics: 16
      Replies: 32
      Points: 80
      Rank: Member

      Thanks again Rob!!
      I have been tinkering with this all morning and starting to bang my head on my desk.
      I did try your suggestion and I am getting a cmdlet error

      I have also tried several other variations.
      I will continue to search the web but thought I would post again so that if you or someone more knowledgeable may find a solution faster than I do.

      Thanks a bunch!!!!

    • #56597
      Participant
      Topics: 16
      Replies: 32
      Points: 80
      Rank: Member

      HA HA!!
      I did it.
      Just in case anyone would like to know how

    • #56608
      Participant
      Topics: 16
      Replies: 1792
      Points: 3,290
      Helping Hand
      Rank: Community Hero

      Glad you got it working. The error you received in the post before, I wanted to show you how to make modular code. Using functions, you could re-use this code again, call it multiple times, etc. versus having the script read line be line. Here is an example for you to play with if you’re interested:

    • #84983
      Participant
      Topics: 0
      Replies: 1
      Points: 0
      Rank: Member

      I want to use this code for multiple SQL queries and want to store the results in different CSV files.
      I tried creating different queries but the result shows only the first query result.
      Need your urgent help ! thanks !

      I managed to get “Oracle.ManagedDataAccess.dll” on my Windows machine and below code worked

      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()

    • #85073
      Participant
      Topics: 16
      Replies: 32
      Points: 80
      Rank: Member

      Hi Shreyas,
      I think the problem you have is that you are writing the results to the file name.
      Try adding a verable to the CSV file name like a date/time or just another name like Dataset1.csv and Dataset2.csv

      Hope that helps.

Viewing 9 reply threads
  • The topic ‘PS script to get data from Oracle DB and export to CSV’ is closed to new replies.