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

PowerShell Help
1

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?

2

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.

3

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

4

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

5

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?

6

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

7

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
8

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.

9

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). :slight_smile: