Please help to find data from excel column

i am new to powershell, please share some guidance on how to connect excel file using power shell and find data from excel sheet.

Also if i can get some help on good training material scripting for beginner

This article is a good start. It uses an Excel module to simplify things. If you can’t import a module, it can still be done but a little more complicated.

As far as training material, I think Don Jones’ and Jeff Hicks’ PowerShell in a Month of Lunches is the go to.

2 Likes

The simplest solution to get data out of an Excel spreadsheet and into Powershell is to convert the sheet into a CSV.

$data = Import-Csv -Path C:\Scripts\MyConvertedFile.csv

do i need to install any module in my machine ?

if you are already dealing with csv, what @rob-simmers has mentioned is the best and easiest approach. Otherwise I would suggest PowerShell Gallery | ImportExcel 7.1.1

i guess all the details you are helping is bouncing for me since i am new to poweshell. for CSV file do i need to download any modules ? how i can find what all modules installed in my system ?

No extra modules are required for Import-Csv, it is a built-in utility for Powershell:

PS C:\Users\rasim\Dropbox\GitHub\Powershell\RemedyForce> Get-Command Import-Csv


CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Cmdlet          Import-Csv                                         7.0.0.0    Microsoft.PowerShell.Utility

Thank you got it. so how can i find any column data in CSV file using PS ?

There are many examples of Import-Csv and basic Powershell functionality on the interwebs. There are books like Month of Lunches that walk you through from start to finish, in a month of lunches, to help you understand the core concepts of Powershell.

Powershell also has built-in help:
Get-Help Import-Csv -Online

There are about articles:
about_Foreach - PowerShell | Microsoft Docs

Use the resources to search and answer you questions. Here is a basic example, but there are many many many examples of this even in this Powershell forum:

PS C:\Users\rasim> 
#Emulate Import-Csv
$csv = @"
Column1,Column2,Column3,Column4
Data1,Data2,Data3,Data4
Data5,Data6,Data7,Data8
Data9,Data10,Data11,Data12
"@ | ConvertFrom-Csv

foreach ($row in $csv) {
    "Doing something with {0} and {1}" -f $row.Column1, $row.Column4
}

Doing something with Data1 and Data4
Doing something with Data5 and Data8
Doing something with Data9 and Data12

so this script will help to search for particular data In CSV File ?

like say i have 3 column i want to search employee ID which i input from command prompt and get result ?

No, @rob-simmers script demonstrates how you can access data in a csv using a foreach loop. If you want to prompt the user data and filter the csv to find the data the user entered, you can use Read-Host to get data from the user and Where-Object to filter the result. Here is a simple example:

#Test Data CSV File
$TestFile = New-TemporaryFile
@"
EmployeeID,FirstName,LastName,Office
1,Joe,Smith,Accounting
2,Sally,Jones,Operations
3,Jane,Lee,Shipping
"@ | Set-Content -Path $TestFile.FullName

#Example Script
$RequestedID = Read-Host -Prompt "Enter Employee ID"
Import-Csv -Path $TestFile.FullName |
    Where-Object {$_.EmployeeID -eq $RequestedID}

Recommend looking at

Get-Help Import-Csv -Online
Get-Help Where-Object -Online
Get-Help Read-Host -Online

i got this thanks understood but this data is coming from test file variable and from printed content…how i can supply file and and from that file i can check for employe id ?

this is my code and output works fine

$testvar = Import-CSV -Path ‘I:\Test File.csv’
RequestedID = Read-Host -Prompt "Enter Employee ID" foreach (_ in $testvar)

{ if
($.SID -eq "RequestedID") { | Format-Table}
}

however when i run all this in PS script it ask for employee ID and at same time it runs foreach loop as well and ask me to enter sid
how can we stop at enter SID and then once enter sid it should show result

Recommend you get a book to learn the fundamentals of Powershell, but here is one approach:

$csv = @"
EmployeeID,FirstName,LastName,Office
1,Joe,Smith,Accounting
2,Sally,Jones,Operations
3,Jane,Lee,Shipping
"@ | ConvertFrom-Csv

$empId = Read-Host -Prompt "Enter Employee Id"

$results = $csv | Where{$_.EmployeeId -eq $empId}

if ($results) {
    $results
}
else {
    "Employee ID {0} not found" -f $empId
}

thank you i have tried using CSV file it worked. one question i have we have more than 60 columns but result is showing limited to 4-5 column is there any reason for this ?

Yes, space for presentation. Powershell will try to present the data in the most readable format. Typically if the data will not fit in table format:

PS C:\Users\rasim> $csv | Select EmployeeId,FirstName,LastName,Office


EmployeeID FirstName LastName Office
---------- --------- -------- ------
1          Joe       Smith    Accounting
2          Sally     Jones    Operations
3          Jane      Lee      Shipping

Then it will change to list format:

PS C:\Users\rasim> $csv


EmployeeID : 1
FirstName  : Joe
LastName   : Smith
FullName   : Joe Smith
Office     : Accounting
Department : Accounting
Site       : X

EmployeeID : 2
FirstName  : Sally
LastName   : Jones
FullName   : Sally Jones
Office     : Operations
Department : Operations
Site       : Y

EmployeeID : 3
FirstName  : Jane
LastName   : Lee
FullName   : Jane Lee
Office     : Shipping
Department : Shipping
Site       : Z

If you try

$csv |  Select -Property * -First 1

it should show all properties for the first record.

That’s another question that is going to be answered very early on if you follow Rob’s advice below.

1 Like