Hey, Scripting Guy! I have an Microsoft Office Excel spreadsheet with server names in it, and I would like to be able to use this in a Windows PowerShell script. The thing is, I do not have Microsoft Office or Excel installed on the server. How can I use this spread sheet without installing Office on my server (which I understand is unsupported anyway)?

– FE

Hi FE,

So you want to be able to use Excel, but you do not want to install Excel? Sounds like one of those riddles I heard when I was a kid. You know those things like, “If you take a red crayon and color all the pages in the book so that no one can see any of the writing, is it a red book?” (Or is it a read book? Homophones can be so confusing. Especially triple homophones such as: so, sew, sow. This is cool: I just figured out that Microsoft Word seems to hate homophones, it is underlining all kinds of stuff with squiggly lines write now. One guy on the Web has collected 441 of the things; one nicety about such a collection is that it does not occupy as much space as a book collection or a car collection.)

Often when working with Excel from within a scripting language, we tend to use the Excel Automation Model. Using the Excel Automation Model generally involves creating an instance of the Excel.Application object. This would look something like the script seen here:

$strPath="c:\data\ScriptingGuys\Servers.xls"
$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$false
$WorkBook=$objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("ServerList")
$intRowMax =  ($worksheet.UsedRange.Rows).count
$Columnnumber = 1

for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
{
 $name = $worksheet.cells.item($intRow,$ColumnNumber).value2
 "Querying $name ..."
  Get-WmiObject -Class win32_bios -computername $name
   }
$objexcel.quit()

But, FE, you specifically stated you did not want to use the Excel object model to query the spread sheet. You are then left with using Active X Data Objects (ADO). There are two flavors of ADO: the classic COM ADO you may have used in VBScript, and ADO.NET. In general, the ADO.NET flavor is faster and provides more options. With both the COM version of ADO and ADO.NET you will need to specify a provider. In some cases, these provider names will be the same. In other cases, you will find providers for a specific flavor of ADO.

In the script we create two objects. The first is the System.Data.OleDb.OleDbConnection object and the second is theSystem.Data.OleDb.OleDbCommand object. You will note that both of the objects are in the System.Data.OleDb namespace. Only the last portion of each name actually changes. These correspond to the connection and the command object we used in classic COM ADO. After the two objects are created, we associate the connection with the command object, open the connection, and use theExecuteReader method from the command object. We then use the data reader to work our way through the data that is returned. We select the name column, pass it to WMI, and close both data reader and connection. The script is shown here:

$strFileName = "C:\Data\scriptingGuys\Servers.xls"
$strSheetName = 'ServerList$'
$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()

While($DataReader.read())
{
 $ComputerName = $DataReader[0].Tostring() 
 "Querying $computerName ..."
 Get-WmiObject -Class Win32_Bios -computername $ComputerName
}  
$dataReader.close()
$objConn.close()

Let’s look at the script in a bit more detail. We begin the script by initializing a bunch of variables. The first is the path to the Excel spread sheet. (Note: We do not test to ensure this path actually exists. We left it out to reduce the complexity of the script. In a real script you would certainly want to verify the presence of the file before attempting to read the file. To do this in PowerShell you would use the Test-Path cmdlet.) We then specify the sheet name. As seen in this figure, I always like to rename the Excel spreadsheets (I also delete the two extra sheets that I never seem to use as well):

Next we need to specify the provider name, data source, and extended parameters for our connection. These are exactly the same as you would have used in classic COM ADO. Last, we need to specify our query. This takes the form of “Select * from [sheetname]”. Note that the spreadsheet name must go inside square brackets (square brackets, not square pants). This section of the script is shown here:

$strFileName = "C:\Data\scriptingGuys\Servers.xls"
$strSheetName = 'ServerList$'
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties=Excel 8.0"
$strQuery = "Select * from [$strSheetName]"

The next thing we need to do is to create the two objects we will be using. When we create the OleDbConnection object we specify the provider, data source, and the extended parameters to the New-Object command. These values are called the constructor as it is used in constructing the connection object. Next we need to create the command object. The OleDbCommand object accepts the query for its constructor. After we have these two objects created, we need to wire up the connection and the command, open the connection, and use the ExecuteReader method from the OleDbCommand object to return our information. This section of the code is shown here:

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

The object that comes back from the ExecuteReader method is called a DataReader. To work with a DataReader, we need to use theRead method. We use the While statement that says as long as the DataReader is returning data, we will keep on reading. This line of code is shown here:

While($DataReader.read())

Well, what are we going to do while we have data coming from the DataReader? We are going to take the first piece of data and turn it into a string. This will be our computer name from the spreadsheet. We then use it in a basic WMI query. To do the WMI query, we use the Get-WmiObject cmdlet and give it the WMI class to query (Win32_Bios) and the name of the computer to query (the one from the spreadsheet). This is seen here:

$ComputerName = $DataReader[0].Tostring() 
 "Querying $computerName ..."
 Get-WmiObject -Class Win32_Bios -computername $ComputerName

To be polite, we close out the DataReader, and we close our connection to the Excel spreadsheet. This is easy to do; we use the closemethod as shown here:

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

The results from this script are impressive because of the minimalistic approach to data return. With our background, we are trying to exhaust the blues whilst understating the induction of white (for all you art majors). This can clearly be seen in this figure:

See, FE, that was not too bad was it? This is just half of the equation. If we happen to have an Excel spreadsheet handy, we can use this method to query it. However, if you do not have one handy, we will need to write to the spreadsheet and populate it with data. We will do that next week. Until then, TTFN.

Ed Wilson and Craig Liebendorfer, Scripting Guys