Coming Soon: GET-IT: Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET Coming Soon: GET-IT: Endpoint Management 1-Day Conference on September 28th at 9:30 AM ET

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

Say Goodbye to Traditional PC Lifecycle Management

Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.

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. […] 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

Live Webinar: Active Directory Security: What Needs Immediate Priority!Live on Tuesday, October 12th at 1 PM ET

Attacks on Active Directory are at an all-time high. Companies that are not taking heed are being punished, both monetarily and with loss of production.

In this webinar, you will learn:

  • How to prioritize vulnerability management
  • What attackers are leveraging to breach organizations
  • Where Active Directory security needs immediate attention
  • Overall strategy to secure your environment and keep it secured

Sponsored by: