Home/ Questions/Q 6425
Next
In Process
Computing Staff
  • 0
Asked: 2021-11-19T07:39:37+00:00 In:

Find An Entry In Excel File Powershell

  • 0

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

Share

    3 Answers

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

      • 0

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

      • 0
    3. 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

      • 0