Read Excel file data using PowerShell script

We quite often need to read data from Excel file (.xlsx) and perform some action on it. In this article I’ll explain how to open Excel file in PowerShell and read data from it.

I want read below data available in my sample excel file using PowerShell script.

Let’s have a look on below PowerShell script which reads data from sample excel file.

PS Script - 

#select excel file you want to read

$file = "C:\PowerShell\MyContacts.xlsx"

$sheetName = "Sheet1"

#create new excel COM object

$excel = New-Object -com Excel.Application

#open excel file

$wb = $excel.workbooks.open($file)

#select excel sheet to read data

$sheet = $wb.Worksheets.Item($sheetname)

#select total rows

$rowMax = ($sheet.UsedRange.Rows).Count

#create new object with Name, Address, Email properties.

$myData = New-Object -TypeName psobject

$myData | Add-Member -MemberType NoteProperty -Name Name -Value $null

$myData | Add-Member -MemberType NoteProperty -Name Address -Value $null

$myData | Add-Member -MemberType NoteProperty -Name Email -Value $null

#create empty arraylist

$myArray = @()

for ($i = 2; $i -le $rowMax; $i++)

{

    $objTemp = $myData | Select-Object *

   

    #read data from each cell

    $objTemp.Name = $sheet.Cells.Item($i,1).Text

    $objTemp.Address = $sheet.Cells.Item($i,2).Text

    $objTemp.Email = $sheet.Cells.Item($i,3).Text

    #Write-Host 'Name-' $objTemp.Name 'Address-' $objTemp.Address 'Email-' $objTemp.Email

   

    $myArray += $objTemp

}

#print $myarry object

#$myArray

#print $myarry object with foreach loop

foreach ($x in $myArray)

{

    Echo $x

}

$excel.Quit()

#force stop Excel process

Stop-Process -Name EXCEL -Force

Output –

You can download code from Gist.

Thank you for reading this article. Please leave your feedback in comments below.

Reference –

MSDN

See Also –

Introduction to Windows PowerShell

Working with File and Folders using PowerShell

Advanced Files and Folders techniques with PowerShell

Get-Process cmdlet in PowerShell