Read and Get Values from Excel File using PowerShell Script

What do I use to read data or values from excel file?  And what if I want to read every row on multiple columns? Example data is like this....

NAME	AGE	CITY
John	22	Chicago
Edward	36	Houston
Michael	57	Brooklyn

First we declare the path where the excel file is stored.  Also, declare the sheet name.

$file = "C:\Documents\ExcelFolder\ExcelFile.xls"

$sheetName = "Sheet1"

After that, create an instance using the COM Object Excel Application to open the excel file.

$objExcel = New-Object -ComObject Excel.Application

$workbook = $objExcel.Workbooks.Open($file)

$sheet = $workbook.Worksheets.Item($sheetName)

$objExcel.Visible=$false

We will also need the number of Rows that has values. We will need these to loop and check every value on every column.

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

And now declare the starting position for each column

$rowName,$colName = 1,1

$rowAge,$colAge = 1,2

$rowCity,$colCity = 1,3

We will now loop through each row and store each variable that you can use for anything you want (i.e. e-mail body variables)

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

{

$name = $sheet.Cells.Item($rowName+$i,$colName).text

$age = $sheet.Cells.Item($rowAge+$i,$colAge).text

$city = $sheet.Cells.Item($rowCity+$i,$colCity).text

}

For the last line of code, we will close the excel file after opening and reading it. If we don’t close it, it will be locked for editing.

$objExcel.quit()

You can check the output by using Write-Host. For the full code below:

#Declare the file path and sheet name

$file = "C:\Users\kfeb\Documents\Textfile\ExcelFile.xlsx"

$sheetName = "Sheet1"

#Create an instance of Excel.Application and Open Excel file

$objExcel = New-Object -ComObject Excel.Application

$workbook = $objExcel.Workbooks.Open($file)

$sheet = $workbook.Worksheets.Item($sheetName)

$objExcel.Visible=$false

#Count max row

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

#Declare the starting positions

$rowName,$colName = 1,1

$rowAge,$colAge = 1,2

$rowCity,$colCity = 1,3

#loop to get values and store it

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

{

$name = $sheet.Cells.Item($rowName+$i,$colName).text

$age = $sheet.Cells.Item($rowAge+$i,$colAge).text

$city = $sheet.Cells.Item($rowCity+$i,$colCity).text

Write-Host ("My Name is: "+$name)

Write-Host ("My Age is: "+$age)

Write-Host ("I live in: "+$city)

}

#close excel file

$objExcel.quit()