# Please help to find data from excel column **URL:** \ **Category:** PowerShell Help\ **Created:** [March 11, 2021, 11:35am UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934 "2021-03-11T11:35:07Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 11, 2021, 11:35am UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/1 "2021-03-11T11:35:07Z")
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 ---
**Author:** ![ralphmwr](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/ralphmwr/32/1064_2.png) [@ralphmwr](https://forums.powershell.org/u/ralphmwr)\ **Post date:** [March 11, 2021, 1:00pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/2 "2021-03-11T13:00:55Z")
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. > **[PowerShell and Excel: Seize the Power!](https://adamtheautomator.com/powershell-excel/)** > > Learn how to use PowerShell and Excel together with the ImportExcel module. Create create charts, pivot tables and leverage other powerful features of Excel. As far as training material, I think Don Jones’ and Jeff Hicks’ PowerShell in a Month of Lunches is the go to. > **[Learn PowerShell Scripting in a Month of Lunches](https://www.manning.com/books/learn-powershell-scripting-in-a-month-of-lunches)** > > GET MORE WITH MANNING > > An eBook copy of the previous edition, Learn PowerShell Toolmaking in a Month of Lunches, is included at no additional cost. It will be automatically added to your Manning account within 24 hours of purchase. > > Discover how... ---
**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\ **Post date:** [March 11, 2021, 1:55pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/3 "2021-03-11T13:55:06Z")
The simplest solution to get data out of an Excel spreadsheet and into Powershell is to convert the sheet into a CSV. ```auto $data = Import-Csv -Path C:\Scripts\MyConvertedFile.csv ``` ---
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 11, 2021, 2:02pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/4 "2021-03-11T14:02:21Z")
do i need to install any module in my machine ? ---
**Author:** ![kvprasoon](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/kvprasoon/32/4449_2.png) [@kvprasoon](https://forums.powershell.org/u/kvprasoon)\ **Post date:** [March 11, 2021, 2:47pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/5 "2021-03-11T14:47:16Z")
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](https://www.powershellgallery.com/packages/ImportExcel/7.1.1) ---
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 11, 2021, 5:04pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/6 "2021-03-11T17:04:37Z")
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 ? ---
**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\ **Post date:** [March 11, 2021, 5:08pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/7 "2021-03-11T17:08:36Z")
No extra modules are required for Import-Csv, it is a built-in utility for Powershell: ```auto 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 ``` ---
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 11, 2021, 5:47pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/8 "2021-03-11T17:47:38Z")
> [@rob-simmers](#): > > `Get-Command Import-Csv` Thank you got it. so how can i find any column data in CSV file using PS ? ---
**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\ **Post date:** [March 11, 2021, 6:01pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/9 "2021-03-11T18:01:00Z")
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](https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_foreach?view=powershell-7.1) 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: ```auto 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 ``` ---
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 29, 2021, 12:54pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/10 "2021-03-29T12:54:29Z")
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 ? ---
**Author:** ![ralphmwr](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/ralphmwr/32/1064_2.png) [@ralphmwr](https://forums.powershell.org/u/ralphmwr)\ **Post date:** [March 29, 2021, 1:14pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/11 "2021-03-29T13:14:32Z")
> [@nik3387](#): > > so this script will help to search for particular data In CSV File ? 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: ```auto #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 ```auto Get-Help Import-Csv -Online Get-Help Where-Object -Online Get-Help Read-Host -Online ``` ---
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 29, 2021, 5:39pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/12 "2021-03-29T17:39:49Z")
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 ? ---
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 29, 2021, 6:20pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/13 "2021-03-29T18:20:25Z")
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 ---
**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\ **Post date:** [March 29, 2021, 6:41pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/14 "2021-03-29T18:41:29Z")
Recommend you get a book to learn the fundamentals of Powershell, but here is one approach: ```auto $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 } ``` ---
**Author:** ![nik3387](https://avatars.discourse-cdn.com/v4/letter/n/a5b964/32.png) [@nik3387](https://forums.powershell.org/u/nik3387)\ **Post date:** [March 30, 2021, 7:01pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/15 "2021-03-30T19:01:09Z")
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 ? ---
**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\ **Post date:** [March 30, 2021, 7:12pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/16 "2021-03-30T19:12:08Z")
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: ```auto 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: ```auto 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 ```auto $csv | Select -Property * -First 1 ``` it should show all properties for the first record. ---
**Author:** ![krzydoug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/krzydoug/32/1008_2.png) [@krzydoug](https://forums.powershell.org/u/krzydoug)\ **Post date:** [March 30, 2021, 10:28pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/17 "2021-03-30T22:28:48Z")
> [@nik3387](#): > > 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 ? That’s another question that is going to be answered very early on if you follow Rob’s advice below. > [@rob-simmers](#): > > Recommend you get a book to learn the fundamentals of Powershell ---
**Author:** ![Florian](https://avatars.discourse-cdn.com/v4/letter/f/898d66/32.png) [@Florian](https://forums.powershell.org/u/Florian)\ **Post date:** [February 10, 2023, 10:40am UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/18 "2023-02-10T10:40:41Z")
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. ---
**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\ **Post date:** [February 10, 2023, 10:49am UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/19 "2023-02-10T10:49:55Z")
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](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/b/bee50c628238f2c076c0504efb6b15f2fb11f002.gif) \<---- 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 > **[ImportExcel 7.8.4](https://www.powershellgallery.com/packages/ImportExcel/7.8.4)** > > PowerShell module to import/export Excel spreadsheets, without Excel. > Check out the How To Videos https://www.youtube.com/watch?v=U3Ne\_yX4tYo&list=PL5uoqS92stXioZw-u-ze\_NtvSo0k0K0kq It eliminates the need to have an Excel installed and it makes it easier to work with Excel files. ---
**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\ **Post date:** [May 16, 2024, 8:15pm UTC](https://forums.powershell.org/t/please-help-to-find-data-from-excel-column/15934/20 "2024-05-16T20:15:09Z")