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

Get-Process cmdlet in PowerShell

In this article I’ll explain some cmdlets around how to get all active processes running on your computer and start/stop process using PowerShell.

Get-Process

You can get all active and running processes on your computer using below command.

Get-Process | more

You can specify names or ID of processes you want to get information about.

Get-Process -Name Chrome, explorer, powershell

Get-Process -Id 200,1692,7368,6988

You can use wildcard characters to search processes.

Get-Process -Name w*

You can specify local or remote computer name to get processes running on local or remote computer.

Get-Process -ComputerName Mitesh-PC -Name chrome,powershell

You can also use below command to check count of how many processes of single process is running using below command. You can use Group-Object cmdlets to group on property. Before you use group you need to sort data using Sort-Object cmdlet.

Get-Process | Sort-Object Name | Group-Object -Property Name

You can send result to output text file using Out-file cmdlets.

Get-Process | Sort-Object Name | Group-Object -Property Name | Select count, name | Out-File -FilePath C:\PowerShell\Processes.txt

Get-ChildItem process*.txt

You can find a specific process running on computer using where object and you can stop that processes same time via below cmdlets. In below example, I search for processes using CPU more than 50 and name starts with ‘A’ and stop all the processes matching this condition.

Get-Process | Where-Object {$_.CPU -ge 50 -and $_.Name -like 'A*'} | Stop-Process -PassThru

Start-Process

You can start process on your local computer using Start-Process cmdlets.

Start-Process notepad 

You can also specify additional parameters along with start-process.

Start-Process notepad -WindowStyle Maximized

Stop-Process

You can stop any running processes using stop-process cmdlets.

Stop-Process -Name notepad

Stop-Process -ID 9964

Wait-Process

This cmdlets wait for process to be stopped. If you execute this cmdlets for specific process, PowerShell prompt will waits until give process stopped manually.

Debug-Process

This cmdlets debug one or more processes running on local computer. This cmdlets attaches the debugger to running process.

Get-Process powershell

Debug-Process powershell

I hope you have some knowledge about how to get processes and perform some actions around it using various PowerShell cmdlets.

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

Reference –

MSDN

See Also –

Introduction to Windows PowerShell

Working with File and Folders using PowerShell

Advanced Files and Folders techniques with PowerShell