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.

$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

$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

$DataReader = $sqlCommand.ExecuteReader()


"Querying File $Excel ..."

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


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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: