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

briantist · Accepted Answer · 2016-11-16 19:34:46Z

0

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.

edited Nov 16, 2016 at 19:34

answered Nov 16, 2016 at 19:20

briantist

48.3k6 gold badges94 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Matt Over a year ago

If the items were simple strings using -contains / -in against the array of possibilities would work as well without the overhead (and in some cases headache) of regex. Perfect solution regardless though.

Matt Over a year ago

Assuming of course the string were not partials of course.

briantist Over a year ago

Agreed @Matt, I'm definitely a fan of -in and -contains but it seems like that wasn't the case here. Regex is pretty fast though, especially for simple compares like this.

briantist Over a year ago

Would the downvoter care to comment? I'm happy to address any questions or concerns.

fudge Over a year ago

Not sure why he did, You answered my question and gave a more efficient way to solve my issue.

Esperento57 · Accepted Answer · 2016-11-17 06:59:12Z

0

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

edited Nov 17, 2016 at 6:59

answered Nov 16, 2016 at 20:16

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

4 Comments

Esperento57 Over a year ago

-contains not, but variable.contains yes

Esperento57 Over a year ago

contains work with dynamic content when you use like this, give an example of your affirmation please

Matt Over a year ago

@Esperento57 What I mean was what do you do when the source file has 5 lines? Your code currently does not work.

Esperento57 Over a year ago

Ok you'r right, ty Matt. I have modified my code ;)

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related