powershell - Find a value in excel and print it -

i'm stuck powershell once more. here scenario: have excel sheet report in it. within excel file there 2 lines i'm looking for

total number of errors = 0

total number of fatal errors = 0

the problem these 2 lines spaces after "errors" may vary depending on report. issue number of errors (in case 0) in same cell.

what i'm trying find 2 lines , return number of errors. here code looks far. know it's missing stuff , not working. tried lots of regular expressions no luck.

any appreciated.

thank in advance.

$file = "c:\test\setup.xlsx" $excel = new-object -comobject excel.application $excel.visible = $true $workbook = $excel.workbooks.open($file) $worksheets = $workbooks.worksheets $worksheet = $workbook.worksheets.item(1) $range = $worksheet.usedrange $keywords="fatal errors", "errors" $filter = "total number of"+ ($(($keywords|%{[regex]::escape($_)}) -join "|")) $search = $range.find($filter) if($search -match '\d[0]')     {     "no errors found"     "no fatal errors found"     } else     {     "number of errors found = " $search     "number of fatal errors found = " $search      } 

so i'll start off saying regex looking "total number offatal errors" or "total number oferrors" because have no space after 'of'. alone might fix it.

"total number of (?:fatal )?errors\s*=\s*(\d*)" 

that's regex i'll using. see in action here

to honest wouldn't rely on excel find it. i'd tell powershell find it, , how i'd it:

$file = "c:\test\setup.xlsx" $excel = new-object -comobject excel.application $excel.visible = $true $workbook = $excel.workbooks.open($file) $worksheets = $workbooks.worksheets $worksheet = $workbook.worksheets.item(1) $range = $worksheet.usedrange $errors = 0 $fatalerrors = 0 $range.cells.value2 | where{$_ -match "total number of (?:fatal )?errors\s*=\s*(\d*)"} | foreach{     switch($matches[0]){         {$_ -like "*fatal*"}{$fatalerrors+=$matches[1];continue}         default{$errors+=$matches[1]}     } }  if($errors -gt 0){     "number of errors found = " + $errors }else{     "no errors found" }  if($fatalerrors -gt 0){     "number of errors found = " + $fatalerrors }else{     "no fatal errors found" } 

results (accurate test spreadsheet setup):

number of errors found = 23 number of errors found = 7 

changed cell values have 3 spaces after error , set number 2, , set fatal errors 0. results:

number of errors found = 2 no fatal errors found 

edit: ok, switch might confusing guess if aren't familiar it. it's loop, , works this:

switch(<array loop through>){     condition  {action if condition true}     condition2 {action if condition2 true}     default {action take if no conditions true} } 

so in our case have 1 condition checking see if cell in statement contains word 'fatal'. if adds matching text $fatalerrors variable. if doesn't have fatal, goes default , adds match $errors. matching text being number @ end of string.