none
Where-Object Question RRS feed

  • Question

  • I'm trying to filter out some data in a csv flie I'm reading to generate another file but I can't seem to get my where to work.

    In a nut shell, I want to select all users that are missing some pieces of data (an email address and a manger) but for some user types (students and residents) I want to filter them out (even though they are missing a manager, if they have an e-mail address I want it filtered out)

    This is what I tried but it didn't work and they more I look at it the more I think I have the logic wrong but I'm just stumped. I've spent a few hours on it now.

    Import-CSV -Path $inputFile | where { ($_.email -lt "*" -or $_.manager -lt "*" ) -and ($_.usertype -notlike "*student*" -or $_.usertype -notlike "*resident*") -and $_.email -gt "*" ) } | select $userAttributes | export-csv $outputPath

    I also tried doing like a sub-query importing the users I want filtered out into an array and then doing a -notcontains but that didn't work either.

    $filterOut = Import-Csv -Path $inputFile | where { ($_.userType -like "*Student*" -or $_.userType -like "*Resident*") -and $_.email -gt "*" } | select person_id

    Import-CSV -Path $inputFile | where { ($_.email -lt "*" -or $_.manager -lt "*" ) -and $filterOut -notcontains $_.person_id } | select $userAttributes | export-csv $outputPath


    Any ideas? Thanks for the help.


    • Edited by BPurchell Monday, September 15, 2014 8:06 PM
    Monday, September 15, 2014 7:59 PM

Answers

  • That helps.

    Try this:

    Import-CSV -Path $inputFile |
     where { (           
              (-not $_.email ) -or 
              ($_.usertype -notmatch "student|resident" -and -not $_.manager)
             )  } |
      select $userAttributes | export-csv $outputPath

    You want to exclude the student/resident accounts that have an email address, but don't have a manager.  

    The converse of that is that you want to include student/resident account that are missing an email address.

    Add that to the inclusion of any other type missing both a manager and email address, and the result is that you want to include any account that's missing an email address.  Hence:

    (-not $_.email )

    If it's not a student/resident and it's missing the Manager:

    -or
    ($_.usertype -notmatch "student|resident" -and -not $_.manager)


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    • Marked as answer by BPurchell Tuesday, September 16, 2014 2:44 PM
    Tuesday, September 16, 2014 12:34 PM
    Moderator
  • Awesome!! This appears to work :) Thank you! But one more question, I just found out there are different types of Students and Residents so I need to use wild cards but I don't think I can do that with -notmatch

    I tried this but it didn't work (included students and residents that had an email address but not a manager when it should have removed theses from the results set), any suggestions?

    Import-Csv -Path $inputFile |
    	where { (-not $_.email) -or ($_.userType -notlike "*Student*" -or $_userType -notlike "*Resident*") -and -not $_.manager) } |
    	select $userAttributes |
    	Export-Csv -NoTypeInformation -Path $outputFile 

    match is faster and does not need a wildcard\

    Try this:

    'somestudentnme' -match 'student|teacher'
    'someteachernme' -match 'student|teacher'

    That is the beauty of match and notmatch.  It gets it in one parse.


    ¯\_(ツ)_/¯

    • Marked as answer by BPurchell Tuesday, September 16, 2014 2:44 PM
    Tuesday, September 16, 2014 1:40 PM

All replies

  • If I'm reading that right, I think this should work:

    Import-CSV -Path $inputFile |
     where { ($_.email -lt "*") -or  ($_.usertype -notmatch "student|resident" -and $_.manager -lt "*" ) } |
      select $userAttributes | export-csv $outputPath


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    Monday, September 15, 2014 8:22 PM
    Moderator
  • -lt doesn't support wildcards.

    What does emil -lt '*' need to do.  DO you want to test for blanks?

    Where{ $_.Email }

    That will filter out all empty email records.

    Import-CSV $inputFile |
        where{$_.email -and $_.manager } |
        where{ $_.usertype -notmatch 'student|resident' } |
        select $userAttributes |
        export-csv $outputPath


    ¯\_(ツ)_/¯

    Monday, September 15, 2014 8:55 PM
  • -lt doesn't support wildcards.

    What does emil -lt '*' need to do.  DO you want to test for blanks?

    Where{ $_.Email }

    That will filter out all empty email records.

    Import-CSV $inputFile |
        where{$_.email -and $_.manager } |
        where{ $_.usertype -notmatch 'student|resident' } |
        select $userAttributes |
        export-csv $outputPath


    ¯\_(ツ)_/¯

    It does look odd, but * has a lower sort order than any of the valid characters for an email address, so it should work for this application.

    And as near as I can tell from the description, if a student/resident is missing both a manager and an email address, they need to be included.


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "


    Monday, September 15, 2014 9:05 PM
    Moderator
  • Just checking for null is probably faster because no matching is done.  It just checks for a value.  It is also more explicit.If the two nulls fail then we skip all of the other stuff.

    Yes the * will work but seems odd to me.

    Still not sure if it is what the OP means to do. We will see I guess.


    ¯\_(ツ)_/¯

    Monday, September 15, 2014 9:29 PM
  • Thanks all for the suggestions. To clarify:

    I have two queries that use the same input file (they generate two different reports that are saved to csv files).

    1. Pulls a list of users where values in certain fields are not null, for this query I'm using -gt "*" and that seems to work - but it sounds like I can just use $_.value from what you described above to factor out the nulls?

    2. (This is the one I'm having problems with) I want to return a list of users that are missing a manager and an email address (this is where I was using -lt "*" to say if this is null, select it. It was the only way with my knowledge I could get this to work) but there is an exception to this condition. If the user is a student or resident and they are missing a manager value but have an email then exclude them from the report. However, if the student/resident is missing both the manager and email values then they need to be included in the report.

    Does this additional context help?

    Thanks all for your help.


    • Edited by BPurchell Tuesday, September 16, 2014 12:17 PM Addl context
    Tuesday, September 16, 2014 12:13 PM
  • To return all missing manager and email do this:

     where{ -not $_.email -and -not $_.manager } |

    This says select all records where both conditions are empty.  BOTH.  If you want either being empty use "-or".


    ¯\_(ツ)_/¯

    Tuesday, September 16, 2014 12:22 PM
  • That helps.

    Try this:

    Import-CSV -Path $inputFile |
     where { (           
              (-not $_.email ) -or 
              ($_.usertype -notmatch "student|resident" -and -not $_.manager)
             )  } |
      select $userAttributes | export-csv $outputPath

    You want to exclude the student/resident accounts that have an email address, but don't have a manager.  

    The converse of that is that you want to include student/resident account that are missing an email address.

    Add that to the inclusion of any other type missing both a manager and email address, and the result is that you want to include any account that's missing an email address.  Hence:

    (-not $_.email )

    If it's not a student/resident and it's missing the Manager:

    -or
    ($_.usertype -notmatch "student|resident" -and -not $_.manager)


    [string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

    • Marked as answer by BPurchell Tuesday, September 16, 2014 2:44 PM
    Tuesday, September 16, 2014 12:34 PM
    Moderator
  • Awesome!! This appears to work :) Thank you! But one more question, I just found out there are different types of Students and Residents so I need to use wild cards but I don't think I can do that with -notmatch

    I tried this but it didn't work (included students and residents that had an email address but not a manager when it should have removed theses from the results set), any suggestions?

    Import-Csv -Path $inputFile |
    	where { (-not $_.email) -or ($_.userType -notlike "*Student*" -or $_userType -notlike "*Resident*") -and -not $_.manager) } |
    	select $userAttributes |
    	Export-Csv -NoTypeInformation -Path $outputFile 

    Tuesday, September 16, 2014 1:32 PM
  • Awesome!! This appears to work :) Thank you! But one more question, I just found out there are different types of Students and Residents so I need to use wild cards but I don't think I can do that with -notmatch

    I tried this but it didn't work (included students and residents that had an email address but not a manager when it should have removed theses from the results set), any suggestions?

    Import-Csv -Path $inputFile |
    	where { (-not $_.email) -or ($_.userType -notlike "*Student*" -or $_userType -notlike "*Resident*") -and -not $_.manager) } |
    	select $userAttributes |
    	Export-Csv -NoTypeInformation -Path $outputFile 

    match is faster and does not need a wildcard\

    Try this:

    'somestudentnme' -match 'student|teacher'
    'someteachernme' -match 'student|teacher'

    That is the beauty of match and notmatch.  It gets it in one parse.


    ¯\_(ツ)_/¯

    • Marked as answer by BPurchell Tuesday, September 16, 2014 2:44 PM
    Tuesday, September 16, 2014 1:40 PM
  • Import-Csv -Path $inputFile |
         where{ $_.userType -notmatch 'student|resident' -and (-not $_.manager -and -not $_.email) } |
         select $userAttributes |
         Export-Csv -NoTypeInformation -Path $outputFile

    ¯\_(ツ)_/¯

    Tuesday, September 16, 2014 1:44 PM
  • Awesome, so -match and -nomatch reads a string and looks to see if what is in the match is contained within the it?

    For example, a string contains"Visiting Student" or "Visiting Resident" and my match would look like

    $_.userType -match "student|resident"

    Would the match come back with $true for "Visiting Student" because "Student" is contained within it. It appears that this is what it is doing and your suggestion is working. I just want to make sure my understanding is correct.

    Tuesday, September 16, 2014 2:06 PM
  • HELP about_Regular_Expressions

    http://www.regular-expressions.info/


    ¯\_(ツ)_/¯

    Tuesday, September 16, 2014 2:47 PM