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.
2 Likes
Hereās a basic example of how you can connect to an Excel file using PowerShell and extract data from it:
Load the Microsoft.Office.Interop.Excel assembly
Add-Type -AssemblyName Microsoft.Office.Interop.Excel
Create an instance of the Excel application object
$excel = New-Object -ComObject Excel.Application
Open the Excel workbook
$workbook = $excel.Workbooks.Open(āC:\path\to\your\file.xlsxā)
Select the first worksheet in the workbook
$worksheet = $workbook.Sheets.Item(1)
Select the used range of cells in the worksheet
$usedRange = $worksheet.UsedRange
Loop through each row in the used range
for ($row = 1; $row -le $usedRange.Rows.Count; $row++) {
# Loop through each column in the used range
for ($column = 1; $column -le $usedRange.Columns.Count; $column++) {
# Get the value of the cell
$value = $usedRange.Cells.Item($row, $column).Value2
# Do something with the value, such as print it
Write-Output "$value"
}
}
Close the workbook
$workbook.Close()
Quit the Excel application
$excel.Quit()
Additionally, there are many online resources and forums, such as Stack Overflow, that can be useful for finding answers to specific questions and troubleshooting issues you may encounter while scripting with PowerShell.
Florian,
Welcome to the forum. 
Thanks for trying top help. But I think it is very unlikely that the questioner is still waiting for an answer. So please try not to reactivate ancient threads. 
Regardless of that ā¦ when you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.
Thanks in advance
How to format code in PowerShell.org <---- Click 
And a another hint for you ā¦ if you want to process Excel files you may take a look at the great module from Doug Finke
It eliminates the need to have an Excel installed and it makes it easier to work with Excel files.