Using arrays with where-object to filter multiple strings out of a csv

Question

I am trying to filter out lines out of a csv that contain any of the values in an array.

Using this post as reference: Use -notlike to filter out multiple strings in PowerShell

I managed to get it working with this format:

Import-Csv "$LocalPath\Stripped1Acct$abbrMonth$Year.csv" | 
    where {$_."SubmitterName" -notlike "*${Report2a}*" 
      -and $_."SubmitterName" -notlike "*${Report2b}*" 
      -and $_."SubmitterName" -notlike "*${Report2c}*"} |
    Export-Csv "$LocalPath\Stripped2Acct$abbrMonth$Year.csv" -NoTypeInformation

Eventually I plan to rewrite the script so it will pull the exclusion list from a text file generated by an end user. In order to do that, I'll have to have it access values in an array. I tried doing that with the following syntax, but it didn't work as intended:

Import-Csv "$LocalPath\Stripped1Acct$abbrMonth$Year.csv" | 
    where {$_."SubmitterName" -notlike "*${Report2[0]}*" 
      -and $_."SubmitterName" -notlike "*${Report2[1]}*" 
      -and $_."SubmitterName" -notlike "*${Report2[2]}*"} |
    Export-Csv "$LocalPath\Stripped2Acct$abbrMonth$Year.csv" -NoTypeInformation

I have a feeling it's just an syntax issue, but after playing around with it for far too long, I've run out of ideas. I have a feeling it's a syntax issue

Answer 1

This is a syntax issue. The ${Name} syntax is used primarily for names that contain odd characters, like ${A ~*Strange*~ Variable Name}. It's not an expression though, so you can't index into it with [0] inside the braces; that would be taken as a literal part of the variable name.

Instead you can use a sub-expression $(...) to do this:

"*$($Report2[0])*"

As an alternative approach, I might convert your whole array into a single regular expression and then use the -match (or -notmatch) operator:

$regex = $Report2.ForEach({ [RegEx]::Escape($_) }) -join '|'

Import-Csv "$LocalPath\Stripped1Acct$abbrMonth$Year.csv" | 
    where {$_."SubmitterName" -notmatch $regex} |
    Export-Csv "$LocalPath\Stripped2Acct$abbrMonth$Year.csv" -NoTypeInformation

This takes the $Report2 array, then builds an array of the same values, but escaped for Regular Expressions (so that any special characters are matched literally), and then builds a regex that looks like:

Item1|Item2|Item3

In RegEx, a pipe is alternation, so it looks for a match of Item1 or Item2, etc. Regex finds it anywhere in the string so it doesn't need a wildcard character the way that -like does.

So with that built to pre-contain all items in your array, then you can use -notmatch to achieve the same thing, and you don't have to hardcode a bunch of indices.

Answer 2

you can use contains too like this

short version

[string[]]$listexludevalue=Get-Content "C:\temp\exludevalue.txt"
Import-Csv "$LocalPath\Stripped1Acct$abbrMonth$Year.csv" | %{$valcontain=$true; $col=$_.Owner; $listexludevalue.ForEach({$valcontain=$valcontain -and !$col.Contains($valuetotest)}); if ($valcontain) {$_} } | Export-Csv "$LocalPath\Stripped2Acct$abbrMonth$Year.csv" -NoTypeInformation

detailed version :

$listexludevalue=Get-Content "C:\temp\exludevalue.txt"

Import-Csv "$LocalPath\Stripped1Acct$abbrMonth$Year.csv" | 
% {

    $valcontain=$true

    foreach ($valuetotest in $listexludevalue) {$valcontain=$valcontain -and !$_.SubmitterName.Contains($valuetotest)}

    if ($valcontain) {$_}

   } | Export-Csv "$LocalPath\Stripped2Acct$abbrMonth$Year.csv" -NoTypeInformation