Searching Excel spreadsheet for text

Welcome Forums General PowerShell Q&A Searching Excel spreadsheet for text

Viewing 4 reply threads
  • Author
    Posts
    • #196823
      Participant
      Topics: 42
      Replies: 170
      Points: 604
      Helping Hand
      Rank: Major Contributor

      I'm trying to search an Excel column for specific text, I pass the workbook name, sheet number and column letter and the text I'm searching for but it is not found. I know the text I'm searching for is in the column, why is it not being found, what am i doing wrong?

      $SearchText = 'I/O operation has been aborted'
      $File = 'C:\Iain\Empyrean\DBAReport\DBAReport_20200106_070005.xlsx'
      $Excel = New-Object -ComObject Excel.Application
      $Workbook = $Excel.Workbooks.Open($File)
      $workSheet = $Workbook.Sheets.Item(5)
      $Range = $Worksheet.Range("F1").EntireColumn
      $Range.Find($SearchText)
      Example of  entry that I'm searching for:
      Time ComputerName LevelDisplayName EventID ProviderName Message
      1/4/2020 18:16 P01 Error 18210 MSSQL$PROD03 BackupIoRequest::ReportIoError: write failure on backup device '//P03/SQLBackups/PROD03/csprod/P03_PROD03_csprod_Full_202001041721.safe'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

       

    • #196874
      Moderator
      Topics: 3
      Replies: 85
      Points: 453
      Helping Hand
      Rank: Contributor

      It looks like your spaces are actually the middle dot (interpunct) character (either U+2E31 or U+00B7). Are you using Notepad++ with the show invisible characters option turned on?

      When I copied your code, searching for the string

      I/O operation has been aborted

      does not work, however if I retype the string

      I/O operation has been aborted

      locally, it does work. I think the Find() method for Excel is searching for the literal interpunct character and not finding it.

      *Edit* Also, just FYI, you have your $workSheet variable as workSheet on line 6 but Worksheet on line 7. This isn't affecting your functionality, it's just inconsistent.

    • #196901
      Participant
      Topics: 42
      Replies: 170
      Points: 604
      Helping Hand
      Rank: Major Contributor

      The dots are added when I copy & paste from VS Code. But I re-wrote the entire script in Powershell_ise and still get no results.

      $SearchText = 'I/O operation has been aborted'
      $File = 'C:\Iain\Empyrean\DBAReport\DBAReport_20200106_070005.xlsx'
      $Excel = New-Object -ComObject Excel.Application
      $Workbook = $Excel.Workbooks.Open($File)
      $workSheet = $Workbook.Sheets.Item(5)
      $Range = $Worksheet.Range("F1").EntireColumn
      $Range.Find($SearchText)
      
      

      The problem seems to be that my script is only reading row 1 of F column. I want to read all the rows searching for the text....?

       

       

      • This reply was modified 1 week, 5 days ago by Iain.
      • This reply was modified 1 week, 5 days ago by Iain.
    • #196910
      Participant
      Topics: 42
      Replies: 170
      Points: 604
      Helping Hand
      Rank: Major Contributor

      I've got it working, by adding this

      
      $Rows = ($worksheet.UsedRange.Rows).count
      for ($i=1; $i -le $Rows -1; $i++) {
      $row = "f$i"
      $Range = $Worksheet.Range($row).EntireColumn
      $Range.Find($SearchText)
      }
      
      
      • This reply was modified 1 week, 5 days ago by Iain.
      • This reply was modified 1 week, 5 days ago by Iain.
      • This reply was modified 1 week, 5 days ago by Iain.
    • #197813
      Moderator
      Topics: 3
      Replies: 85
      Points: 453
      Helping Hand
      Rank: Contributor

      After doing some further reading, it seems that Find returns the first match that it discovers in the specified range and then stops, so using EntireColumn just tells Find to check the whole column for a match, not to find every match in the column. However, you can re-use the parameters of a Find search with the FindNext method (which is basically analogous to the way the Find dialogue works in the Excel application).

      Here is an example script in which the author implements FindNext in a Do loop after an initial Find search in order to find all matches in the desired search range.

Viewing 4 reply threads
  • You must be logged in to reply to this topic.