# Find an entry in excel file powershell

July 24, 2014 at 14:48:16
Specs: Windows 7, Dell percision 530
 I have a text document with servers listed asserver01server02...I have an excel document with a list of serversI would like to take the text document line E.G. server01and look for it in the excel document..Here is the logic I have for taking server01 from text document and searching in excelforeach ($server in$manlist){ while (($ExcelWorkSheet.Cells.Item($Row, $Column).Value() -ne$Null)) { If (($ExcelWorkSheet.Cells.Item($Row, $Column).Value()).ToUpper() -eq$server.ToUpper()) { #^-- Cell value equals $Arg Write-Host ($ExcelWorkSheet.Cells.Item($Row,$Column).value()) $Row++ } else{$Row++ } } }It finds the first match and then no others... There are others and i know my logic is confusing it somewhere. Can anyone helpim using powershell for this$manlist is the text documentLearning in progress..........message edited by vande See More: Find an entry in excel file powershell #1 July 25, 2014 at 10:22:43  I was going to mention you never reset$Row. Then I noticed you're only looking to see if the server is in both Excel and your list, and printing it out if so. That's logic that can be compressed into two lines.$regex = '(?i)^(' + (($manlist | ForEach-Object { [Regex]::Escape($_) }) -join "|") + ')$' ($ExcelWorkSheet.Range("A:A").Value() | Select-Object) -match$regexmessage edited by Razor2.3

Report •

#2
August 12, 2014 at 11:30:47
 I like the not clearing $Rowas for regex expressions.. I would rather not go this route... Not saying it's not an answer , I just think this can be performed without going to regex expressions... Anyone else have an idea Learning in progress.......... Report • #3 August 13, 2014 at 15:57:18  Well, nested loops are probably the slowest way to go about it; everything else is going to use compiled code. Granted, I tend to use something like this on 50K row spreadsheets, your needs might differ from mine. If you want to go with nested loops, just make sure you reset the variables between runs.If you have Access, or at least the Access engine, you can use that to read the workbook without running the full Excel application. It's not a slick 2 lines, but it's probably the fastest way to read an .XLS or .XLSX file, and it's my personal choice. Modify as necessary.$wb = New-Object Data.OleDB.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='c:\path\to\your.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1'") $query = New-Object Data.OleDb.OleDbCommand("SELECT F1 FROM [Sheet1$] WHERE F1 IN ('$($manlist -join "','")')") $query.Connection =$wb $wb.Open()$data = $query.ExecuteReader() if (!$data.IsClosed) { while ($data.Read()) {$data[0] } $data.Close() }$wb.Close()`You can also use Excel's Range.Find to locate your servers. Maybe I'll throw that method up tomorrow.

Report •
Related Solutions