Exclude results of a compare-object if they meet a specific criteria

This topic contains 8 replies, has 3 voices, and was last updated by  Robert Westerlund 3 years, 6 months ago.

  • Author
    Posts
  • #15112

    Mike R
    Participant

    I am using the Compare-object command to compare a CSV file. I am successfully using this to find the difference between two csv files:

     compare-object $yesterdays $todays -Property EMPLID,GROUP_NAME -PassThru|?{$_.SideIndicator -eq '=>'}| 
        ?{$_.GROUP_Name -eq 'FacStaffStudents14Spring' -or $_.GROUP_Name -eq 'Students14Spring'} | select * -ExcludeProperty MIDDLE_INITIAL,SideIndicator| Export-Csv $getquota -NoTypeInformation 

    What I would like to do is filter this output further to exclude objects if the GROUP_NAME from $yesterday to $today was changed from FacStaffStudents14Spring to Students14Spring or from Students14Spring to FacStaffStudents14Spring. Do I need to incorporate if else statements somehow?

  • #15113

    Robert Westerlund
    Participant

    If you could create a runnable sample (I guess what's missing would be a string for $yesterday and a string for $today, where the strings shows of the different cases you've identified), it would make it a lot easier to assist you and to communicate regarding your case.

    • #15133

      Mike R
      Participant

      Ok here is a working code sample:

      $today = [DateTime]::Today.ToString("MM-dd-yyyy")
      $yesterday = [DateTime]::Today.AddDays(-1).ToString("MM-dd-yyyy")
      $yesterdays = Import-Csv -Path ($yesterday +".csv")
      $todays = Import-Csv -Path ($today +".csv")
      $getquota = $today +"_Add_Quota.csv"

      compare-object $yesterdays $todays -Property EMPLID,GROUP_NAME -PassThru|?{$_.SideIndicator -eq '=>'}|
      ?{$_.GROUP_Name -eq 'FacStaffStudents14Spring' -or $_.GROUP_Name -eq 'Students14Spring'} | select * -ExcludeProperty MIDDLE_INITIAL,SideIndicator| Export-Csv $getquota -NoTypeInformation

  • #15135

    Robert Westerlund
    Participant

    Well, the important part in getting a runnable sample was sample content for the actual csv files. Could you please create some sample csv-files to test it with?

    • #15136

      Mike R
      Participant

      Yeah I was attempting to do it with the forum file upload but it does not seem to work. Here are the links to the files from drive:

      05-07-2014.csv
      05-08-2014.csv

  • #15137

    Robert Westerlund
    Participant

    Here's an example of one way to achieve the results you want. There might be more efficient ways, but I find this way pretty easy to read too:

    $yesterdays = Import-Csv -Path ("05-07-2014.csv")
    $todays = Import-Csv -Path ("05-08-2014.csv")
    $getquota = "05-08-2014_Add_Quota.csv"
    
    compare-object $yesterdays $todays -Property EMPLID,GROUP_NAME -PassThru | 
        
        #Group by the employee id in order to compare old and new group names
        Group EmplId |
    
        #To make working with additional clauses (e.g. the where-clause) easier, let's 
        #create an object with the Old and New entries as different properties
        Foreach {
            New-Object PSObject -Property @{
                New = $_.Group | Where { $_.SideIndicator -eq '=>' }
                Old = $_.Group | Where { $_.SideIndicator -eq '< =' }
            }
        } |
        
        #Then just filter the list to get the ones you want
        Where {
            $hasChangedFromFacultyToStudent = $_.Old.Group_Name -eq 'FacStaffStudents14Spring' -and $_.New.Group_Name -eq 'Students14Spring'
            $hasChangedFromStudentToFaculty = $_.Old.Group_Name -eq 'Students14Spring' -and $_.New.Group_Name -eq 'FacStaffStudents14Spring'
            #In this case we only want those that did _not_ change according to either of the two above conditions
            return -Not($hasChangedFromFacultyToStudent -or $hasChangedFromStudentToFaculty)
        } |
        
        #You seem to only care for the new data, so lets throw away the old data
        Select -ExpandProperty New |
        
        #And then select the properties you want
        Select * -ExcludeProperty MIDDLE_INITIAL,SideIndicator | 
        
        #And finally export the CSV
        Export-Csv $getquota -NoTypeInformation
  • #15144

    Mike R
    Participant

    Robert that was what I was looking for. I modified the code a little bit because it was pulling in some extra groups.

    New = $_.Group | Where { $_.SideIndicator -eq '=>' }| ?{$_.GROUP_Name -eq 'FacStaffStudents14Spring' -or $_.GROUP_Name -eq 'Students14Spring'}
    Old = $_.Group | Where { $_.SideIndicator -eq '< =' }| ?{$_.GROUP_Name -eq 'FacStaffStudents14Spring' -or $_.GROUP_Name -eq 'Students14Spring'} }

    Does using the Group EmplId command make the compare-object cmdlet work more efficiently?

    Thanks for your help.

  • #15145

    Robert Westerlund
    Participant

    Okay, I had only the sample csv files to test with, and it seemed to work fine there. Glad you got it working with your dataset too.

    No, the Group-Object does not make the Compare-Object more efficient, since the Compare-Object runs before the Group-Object. However, the Compare-Object will remove all entries which are the same on both sides, so the Compare-Object will make the Group-Object more efficient (given that there are unchanged entries; if everything is changed it won't, of course). 🙂

  • #15126

    Vern Anderson
    Participant

    The bottom line is you will need to use methods before the compare to remove the rows you don't want to see in your results. I don't know of any parameters on the compare-object CMDLET that would help you filter the result.

    Plus filtering as far to the left of the command line as possible follows the best practice golden rules of PowerShell.

    Like Robert said though we could provide some sudo code if you could post a sample CSV.

    http://blogs.technet.com/b/heyscriptingguy/archive/2011/10/17/easily-remove-columns-from-a-csv-file-by-using-powershell.aspx

    http://blogs.technet.com/b/heyscriptingguy/archive/2011/11/01/use-powershell-to-remove-duplicate-lines-from-a-csv-file.aspx

    -VERN

You must be logged in to reply to this topic.