Created
May 8, 2018 14:36
-
-
Save miteshsureja/55b81f9a2dc563d9d34727f5e14aba9a to your computer and use it in GitHub Desktop.
Read data from Excel file using PowerShell script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Output