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 as
server01
server02
.
.
.
I have an excel document with a list of servers
I would like to take the text document line E.G. server01
and look for it in the excel document..

Here is the logic I have for taking server01 from text document and searching in excel

foreach ($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 help

im using powershell for this

$manlist is the text document

Learning in progress..........

message edited by vande


See More: Find an entry in excel file powershell

Report •


#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 $regex

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#2
August 12, 2014 at 11:30:47
I like the not clearing $Row

as 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.

How To Ask Questions The Smart Way


Report •

Related Solutions


Ask Question