title: powershell oracle insert data from text file
body: <p>I am looking to insert data from a tab delimited text file into an Oracle 11g database using a PowerShell script.  I realize there may be better tools or methods to do this but I have been given a task and my tools are limited.  My previous script used a SQLLDR command to load the data but I need to rewrite the script without SQLLDR</p>
  
  <p>So far I have been looking into using some of the ODP .NET functionality as well as OleDBConnections but I seem to run into roadblocks each time. </p>
  
  <p>My hope is to use the OracleDataAcess and OracleDataTable methods in ODP .NET to be able to load data into a table from my tab delimited file.  Unfortunately I am fairly new to Oracle and .NET so I'm not sure how best to get the data loaded.</p>
  
  <p>Here is some of the function that get's me access to Oracle.  The query is just a simple select statement on a table in the database.</p>
  
  <pre><code>            Add-Type -Path “C:\app\g_dawdy\product\11.2.0\client_2\odp.net\bin\2.x\Oracle.DataAccess.dll"
              Add-type -AssemblyName System.Data.OracleClient
              $con = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=$DBUsername;Password=$DBPassword;Data Source=$DBName")     
              $con.Open()
              $cmd = New-Object Oracle.DataAccess.Client.OracleCommand
              $cmd.CommandText = "SELECT * FROM VOIP_VALIDATE "
              $da = New-Object Oracle.DataAccess.Client.OracleDataAdapter($cmd);
  
              $resultSet = New-Object System.Data.DataTable
              [Void]$da.fill($resultSet)
  
              $resultSet | Format-Table -AutoSize
              $con.Close()
  </code></pre>