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.

Sponsored Content

Read the Best Personal and Business Tech without Ads

Staying updated on what is happening in the technology sector is important to your career and your personal life but ads can make reading news, distracting. With Thurrott Premium, you can enjoy the best coverage in tech without the annoying ads.

Excel Data

Figure 1 Excel DataTo open the file, use the Open() method from the Workbooks object.


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.


do {

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 {

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

​New-Object -TypeName PSObject -Property @{
        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.

} 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.


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!


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.

Related Topics:


Don't have a login but want to join the conversation? Sign up for a Petri Account

Comments (1)

One response to “Integrating Microsoft Excel with PowerShell: Reading Data”

  1. Integrating Microsoft Excel with PowerShell: Rich Excel Docs

    […] And we're back with our series on integrating Microsoft Excel with PowerShell. Last time we looked at building a basic report with Microsoft Excel and Windows PowerShell. In some ways, what we created last time isn't that much different than creating a CSV and opening it in Excel. So, if you want to use Excel, let's really use it! In today's post, I'll use the basic demonstration script from last time, but create a richer Excel document. Later, in part three I'll show you how to read data from an Excel file. […]

Leave a Reply

Download this eBook!

External Sharing and Guest User Access in Microsoft 365 and Teams

his eBook will dive into policy considerations you need to make when creating and managing guest user access to your Teams network, as well as the different layers of guest access and the common challenges that accompany a more complicated Microsoft 365 infrastructure. The eBook will also outline some of the major decision points across four general-purpose guest access policy scenarios for how an organization can set this up with standard licensing.

Download Now

Sponsored By