CODEWITHSUNDEEP
arrayspowershellfiltering
user3014016
user3014016

Reputation: 75

Filtering Search Results Against a CSV in Powershell

Im interested in some ideas on how one would approach coding a search of a filesystem for files that match any entries contained in a master CSV file. I have a function to search the filesystem, but filtering against the CSV is proving harder than I expect. I have a csv with headers in it for Name & IPaddr:

#create CSV object
$csv = import-csv filename.csv

#create filter object containing only Name column
$filter = $csv | select-object Name

#Now run the search function 
SearchSubfolders | where {$_.name -match $filter} #returns no results

I guess my question is this: Can I filter against an array within a pipeline like this???

Upvotes: 2

Views: 5294

Answers (5)

user3014016
user3014016

Reputation: 75

I ended up using a 'loop within a loop' construct to get this done after much trial and error:

#the SearchSubFolders function was amended to force results in a variable, SearchResults

$SearchResults2 = @()
foreach ($result in $SearchResults){
  foreach ($line in $filter){
    if ($result -match $line){
      $SearchResults2 += $result
    }
  }
}

This works great after collapsing my CSV file down to a text-based array containing only the necessary column data from that CSV. Much thanks to Ansgar Wiechers for assisting me with that particular thing!!!

All of you presented viable solutions, some more complex than I cared for, nevertheless if I could mark multiple answers as correct, I would!! I chose the correct answer based on not only correctness but also simplicity.....

Upvotes: 0

Mike Zboray
Mike Zboray

Reputation: 40818

You can use Compare-Object to do this pretty easily if you are matching the actual Names of the files to names in the list. An example:

$filter = import-csv files.csv
ls | Compare-Object -ReferenceObject $filter -IncludeEqual -ExcludeDifferent -Property Name

This will print the files in the current directory that match the any Name in files.csv. You could also print only the different ones by dropping -IncludeEqual and -ExcludeDifferent flags. If you need full regex matching you will have to loop through each regex in the csv and see if it is a match.

Here's any alternate solution that uses regular expression filters. Note that we will create and cache the regex instances so we don't have to rely on the runtime's internal cache (which defaults to 15 items). First we have a useful helper function, Test-Any that will loop through an array of items and stop if any of them satisfies a criteria:

function Test-Any() {
    param(
    [Parameter(Mandatory=$True,ValueFromPipeline=$True)]
    [object[]]$Items, 
    [Parameter(Mandatory=$True,Position=2)]
    [ScriptBlock]$Predicate)

    begin { 
        $any = $false 
    }
    process {
        foreach($item in $items) {
            if ($predicate.Invoke($item)) {
                $any = $true
                break
            }   
        }   
    }
    end { $any }
}

With this, the implementation is relatively simple:

$filters = import-csv files.csv | foreach { [regex]$_.Name }
ls -recurse | where { $name = $_.Name; $filters | Test-Any { $_.IsMatch($name) } }

Upvotes: 0

mjolinor
mjolinor

Reputation: 68273

Another option is to create a regex from the filename collection and use that to filter for all the filenames at once:

$filenames = import-csv filename.csv |
 foreach { $_.name }

[regex]$filename_regex = ‘(?i)^(‘ + (($filenames | foreach {[regex]::escape($_)}) –join “|”) + ‘)$’

$SearchSubfolders | 
 where { $_.name -match $filename_regex }

Upvotes: 0

Mark
Mark

Reputation: 301

You need a pair of loops:

#create CSV object
$csv = import-csv filename.csv

#Now run the search function
#loop through the folders
foreach ($folder in (SearchSubfolders)) {
    #check that folder against each item in the csv filter list
    #this sets up the loop
    foreach ($Filter in $csv.Name) {
        #and this does the checking and outputs anything that is matched
        If ($folder.name -match $Filter) { "$filter" }
    }
}

Upvotes: 1

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

Usually CSVs are 2-dimensional data structures, so you can't use them directly for filtering. You can convert the 2-dimensional array into a 1-dimensional array, though:

$filter = Import-Csv 'C:\path\to\some.csv' | % {
            $_.PSObject.Properties | % { $_.Value }
          }

If the CSV has just a single column, the "mangling" can be simplified to this (replace Name with the actual column name):

$filter = Import-Csv 'C:\path\to\some.csv' | % { $_.Name }

or this:

$filter = Import-Csv 'C:\path\to\some.csv' | select -Expand Name

Of course, if the CSV has just a single column, it would've been better to make it a flat list right away, so it could've been imported like this:

$filter = Get-Content 'C:\path\to\some.txt'

Either way, with the $filter prepared, you can apply it to your input data like this:

SearchSubFolders | ? { $filter -contains $_.Name }  # ARRAY -contains VALUE

The -match operator won't work, because it compares a value (left operand) against a regular expression (right operand).

See Get-Help about_Comparison_Operators for more information.

Upvotes: 0

Related Questions