Integrating Microsoft Excel with PowerShell: Reading Data

Welcome back to this three-part series on Integrating Microsoft Excel with PowerShell. In the first 2 parts of this series, we looked at ways of writing data to Excel and creating rich reports, as well as some additional formatting options and other items that take advantage of Microsoft Excel.
The other half of the story for some IT pros is reading data from an Excel file. The challenge is that you have to know in advance the worksheet layout. It is possible to find the data but that’s a much more complicated situation. I’m going to assume that you have an Excel file that you have worked with before and that you know the data you want. In this case, reading the data with PowerShell is not that difficult.

As we did in the first two parts of this series, the first step is to create the Excel application object.

​$xl=New-Object -ComObject "Excel.Application"

I’m going to use this Excel file in my script.
Excel Data
Figure 1 Excel DataTo open the file, use the Open() method from the Workbooks object.

​$wb=$xl.Workbooks.Open($file)
$ws=$wb.ActiveSheet

The $ws object is our main reference point to the data. The data I want to work with starts in A2. In my test scenario, I might not know how many rows of data I need to process, but as long as I know where to start I can use a Do loop to read the row, get the data, and then advance.

​$Row=2
do {
  $data=$ws.Range("A$Row").Text
...

Using the Range property, I can retrieve the cell at A2. The Text property is the cell’s value. My sample script wants to take the computer name from the first column, get some WMI information, and write a custom object to the pipeline that incorporates other data from the spreadsheet.

As you work with Excel data I encourage you to do a lot of validation. Assuming there is a value in the cell, I’m assuming it is a computername so I’ll try to ping it.

​if ($data) {
    Write-Verbose "Querying $data"
      $ping=Test-Connection -ComputerName $data -Quiet

If the ping works, I’ll use WMI to get the operating system name, otherwise I’ll set my $OS variable to $Null.

​if ($Ping) {
        $OS=(Get-WmiObject -Class Win32_OperatingSystem -Property Caption -computer $data).Caption
      }
      else {
        $OS=$Null

Finally, for each computer, I’m creating a custom object using the New-Object cmdlet.

​New-Object -TypeName PSObject -Property @{
        Computername=$Data.ToUpper()
        OS=$OS
        Ping=$Ping
        Location=$ws.Range("B$Row").Text
        AssetAge=((Get-Date)-($ws.Range("D$Row").Text -as [datetime])).TotalDays -as [int]
      }

Notice the other property values I’m setting, such as Location, which is in Row B2, at least for the first computer. It is important to remember that data you get back from the Text property is just that, text. But that doesn’t mean you can’t turn it into different types as I do for AssetAge. I take the text in D2 and treat it as a DateTime object so that I can subtract it from the current date and time to create a TimeSpan object, which has a TotalDays property.

The final step in the loop is to increment the row counter by 1.

​$Row++
} While ($data)

The next time through the loop, the script will be processing data in row 3. This continues until PowerShell encounters an empty row. At the very end I’ll close the file and quit.

​$xl.displayAlerts=$False
$wb.Close()
$xl.Application.Quit()

Running my script produces this output:

​PS C:\scripts> .\Demo-ReadExcel.ps1
AssetAge     : 687
Ping         : True
Computername : SERENITY
Location     : R1-1
OS           : Microsoft Windows 7 Ultimate
AssetAge     : 293
Ping         : True
Computername : QUARK
Location     : R1-4
OS           : Microsoft Windows 7 Professional
AssetAge     : 293
Ping         : False
Computername : SERVER01
Location     : R3-2
OS           :
AssetAge     : 2005
Ping         : True
Computername : JDHIT-DC01
Location     : R2-1
OS           : Microsoft(R) Windows(R) Server 2003, Enterprise Edition

With a few lines of PowerShell, I was able to read data from an Excel spreadsheet and use it in my session. If I had wanted, I could have combined techniques from my earlier articles and updated the spreadsheet at the same time!

Summary

You can download my sample script and Excel file to try this out for yourself. Remember, when writing PowerShell to read Excel files, you will need to know in advance your data layout, and be sure to use a lot of error handling and data validation. Integrating Excel and PowerShell is not something I’d recommend for beginners, but with experience and some patience it can be very rewarding.