PowerShell – read Excel the fast way

In Powershell there are 2 ways of reading excel.

Fast and slow.

The Slow way is using the Excel COM interface.

CLS
$ExcelWB = new-object -comobject excel.application

$ExcelWB.Visible = $True
$Workbook = $ExcelWB.Workbooks.Open("C:\_\Apps\_PowerShell\test.xls") 
$Worksheet = $Workbook.Worksheets.Item(1)
...

The Fast way is using the OLEDB approach

CLS
$strFileName = "C:\_\Apps\_PowerShell\test.xls"
$strSheetName = 'Sheet1$'

$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn
$objConn.open()

$DataReader = $sqlCommand.ExecuteReader()

$DBCounter=$DataReader.FieldCount

"Querying File $Excel ..."

While($DataReader.read())
{
 $Excel = $DataReader[0].Tostring() 
  
 for ($i = 0; $i -lt $DBCounter; $i++) {
  ($DataReader.GetName($i) + " " + $DataReader.GetValue($i))
  }
  echo ' '   
}  

$dataReader.close()
$objConn.close()

So if you have a need for speed, you know which one to choose. Smile

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: