How to Read Excel Sheet Data in PowerShell

One of the commonplace tools that most IT professionals should have used at least once in their employment is Microsoft Excel. But unfortunately, many IT professionals use Excel as a little database storing that lead to tons of data.

This article will teach us the fundamentals of using PowerShell to automate data reading in an Excel workbook.

Read Excel Value in PowerShell

We can save an Excel workbook into a PowerShell object which can then be read and manipulated using the New-Object -ComObject Excel.Application COM command. Ensure you have an Office 2016 package installed on your machine for the commands to work.

Let’s take the Excel file below as an example.

Sample.xlsx:

Name,Salary
John,1000
Paul,2000
Mary,1500
Matt,3000

To find a cell value, open up the Excel workbook to bring it into memory by using the following snippet.

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.workbooks.Open('C:\PS\Sample.xlsx')

Next, pick the worksheet inside of the workbook. The argument accepts an integer value, where 1 is Sheet 1, 2 is Sheet 2, and so on.

$sheet = $workbook.Sheets.Item(1)

Once we assign the worksheet to a variable, we can drill down to individual rows, columns, and cells. Perhaps we need to find cell values for a specific cell.

Then, we need to reference the cells property providing the row and column coordinates.

$cellValue = $sheet.Cells.Item(2, 2).Value2

However, if we don’t want the cell coordinates but its name address, we can use the Address function to display it.

Example Code:

$cellAddress = $sheet.Cells.Item(2, 2).Address()
Write-Output $cellAddress

Output:

$B$2

Now with all of these commands, we can formulate a sample snippet of code that will output all of a specific column’s data using PowerShell.

$rangeAddress = $sheet.Cells.Item(2, 2).Address() + ":" + $sheet.Cells.Item(5, 2).Address()

$sheet.Range($rangeAddress).Value2 | foreach {
    $salary = $_
    Write-Output $salary
}

Output:

1000
2000
1500
3000