PoshCode Logo PowerShell Code Repository

Get-OLEDBData by Chad Miller 4 years ago
View followups from zafer uzun and zafer uzun | embed code: <script type="text/javascript" src="http://PoshCode.org/embed/1591"></script>download | new post

Generic function to execute query and return DataTable from any OLEDB data source. Tested against Excel, Informix, Oracle and SQL Server sources.

  1. ###########################################################################
  2. # Get-OLEDBData
  3. # --------------------------------------------
  4. # Description: This function is used to retrieve data via an OLEDB data
  5. #              connection.
  6. #
  7. # Inputs: $connectstring  - Connection String.
  8. #         $sql            - SQL statement to be executed to retrieve data.
  9. #
  10. # Usage: Get-OLEDBData <connction string> <SQL statement>
  11. #
  12. #Connection String for Excel 2007:
  13. #"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
  14. #Connection String for Excel 2003:
  15. #"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
  16. #Excel query
  17. #'select * from [sheet1$]'
  18. #Informix
  19. #"password=$password;User ID=$userName;Data Source=$dbName@$serverName;Persist Security Info=true;Provider=Ifxoledbc.2"
  20. #Oracle
  21. #"password=$password;User ID=$userName;Data Source=$serverName;Provider=OraOLEDB.Oracle"
  22. #SQL Server
  23. #"Server=$serverName;Trusted_connection=yes;database=$dbname;Provider=SQLNCLI;"
  24. ###########################################################################
  25. function Get-OLEDBData ($connectstring, $sql) {
  26.    $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
  27.    $OLEDBConn.open()
  28.    $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
  29.    $readcmd.CommandTimeout = '300'
  30.    $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
  31.    $dt = New-Object system.Data.datatable
  32.    [void]$da.fill($dt)
  33.    $OLEDBConn.close()
  34.    return $dt
  35. }

Submit a correction or amendment below (
click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me