Searching through large Powershell arrays-CodePudding

Warning: Powershell novice, terms/code may be incorrect

Say I have a Powershell array with data from Invoke-Sqlcmd, say 10,000 "rows" with a half dozen (Col1-Col6) columns each. Say I want to find every row with an empty Col5. Right now I'm doing

foreach ($row in $sqlimport.col1)
 { $col5data = $sqlimport.Where({$_.col1 -eq $row}) | Select-Object -ExpandProperty Col5
if ( '' -eq $col5data ) {$col5data 1} else {} $col5data

Returns 1, which it should in my test. Code seems wrong and slow. It takes several minutes to run through. When something like

$sqlimport.Where({$_.col5 -eq 'somedatathatisthere'})

takes milliseconds

However,

$sqlimport.Where({$_.col5 -eq ''})

Returns blank

($sqlimport.Where({$_.col5 -eq ''})).Count

Returns 0

CodePudding user response：

Right now, you're asking PowerShell to create an array consisting of all the values in column col1, and then for each iteration you search the entire array again to find the corresponding col5 value. That's entirely unnecessary.

Simply loop over the array itself:

foreach($row in $sqlimport){
    if($row.Col5 -like ''){
        Write-Host "Col5 is empty in row with id $($row.Col1)"
    }
}

This only iterates over the entire array once.