Compare-Object giving two different results...why?

I have two CSV files and each of them has multiple columns. Here's an example

# File 1
#
"EMPLOYEE_NUMBER","FIRST_NAME","LAST_NAME","ZIP_CODE"
1,"Sarah","Peters",92856
2,"Karina","Enriquez",98723
3,"Megan","Ryan",23455
4,"Tom","Penn",23562
5,"Leticia","Sanchez",35662

# File 2
"EMPLOYEE_NUMBER","FIRST_NAME","LAST_NAME","DEPT","Title"
1,"Sarah","Peters","Accounting","Rep"
2,"Karina","Enriquez","Finance","Supervisor"
3,"Megan","Ryan","IT","Manager"
4,"Tom","Penn","Ops","General Mgr"
6,"John","Chen","Finance","Rep"

Here are my problems:

1. I use Compare-Object $a $b but get no output back even though each file contains a record that the other does not

2. I use Compare-Object $a $b -IncludeEqual and I get an output...however, the record that only exists in the file shows as being equal between the two objects...doesnt matter if I use $a $b or $b $a...same result.....could this be because each file has multiple columns?? what key is Compare-Object doing the comparison if I dont specify using -Property?

3. However, when I use Compare-Object $a $b -Property EMPLOYEE_NUMBER -IncludeEquals...I get what the output that I expect less the additional columns..but nonetheless I get what I should be getting back.

What am I missing here?


file1-file2.png
file2-file1.png
LVL 2
bnditAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

soostibiCommented:
Actually Compare-Object compares object (it would be better to call collections) by the properties you specify. If you do not specify any property, it convert the elements in both collections to string, and makes the comparison this way.
If you convert one element from a collection made from a csv you'll get nothing. That is why it can  not detect the differences correctly.

So you should specify the properies.
Of course it can compare collections of strings and numbers without problem, without specifying properties (numbers do not have properties). It can also compare object that are converted to some descriptive string, without specifying the properties to compare, but this is not true for custom objects, such as a CSV.

This case you have to specify the common properties: employee_number, first_name, Last_name
$a = @"
"EMPLOYEE_NUMBER","FIRST_NAME","LAST_NAME","ZIP_CODE"
1,"Sarah","Peters",92856
2,"Karina","Enriquez",98723
3,"Megan","Ryan",23455
4,"Tom","Penn",23562
5,"Leticia","Sanchez",35662
"@ | ConvertFrom-Csv

$b = @"
"EMPLOYEE_NUMBER","FIRST_NAME","LAST_NAME","DEPT","Title"
1,"Sarah","Peters","Accounting","Rep"
2,"Karina","Enriquez","Finance","Supervisor"
3,"Megan","Ryan","IT","Manager"
4,"Tom","Penn","Ops","General Mgr"
6,"John","Chen","Finance","Rep"
"@ | ConvertFrom-Csv

Compare-Object $a $b -Property employee_number, first_name, Last_name

Open in new window

0
bnditAuthor Commented:
@soostibi
Thanks for the reply...I'm still trying to wrap my head about your comment regarding 'objects' and 'collections'...kinda makes sense. Ok, so if it's better to specify the properties to do the comparison on...that creates a totally different problem for me because I cannot do this:

Compare-Object $a $b -Property EMPLOYEE_NUMBER, FIRST_NAME, LAST_NAME -IncludeEqual

The reason is because the *only* key that I want to use is EMPLOYEE_NUMBER because FIRST_NAME and/or LAST_NAME may have different values in both files for the same person (William Jones same as Bill Jones...however, both of those records have the same EMPLOYEE_NUMBER). The problem with using EMPLOYEE_NUMBER is that all the other columns disappear...for example, if I use this:

Compare-Object $a $b -Property EMPLOYEE_NUMBER -IncludeEqual

My output is a list of EMPLOYEE_NUMBERS...without the FIRST_NAME, LAST_NAME, DEPT, TITLE, etc.

Also, I noticed you're doing the assignment for each file...and obviously these are sample files...in reality my files are large files and I'm guessing this technique wouldnt work in that case....what other suggestions do you have for me to do the following:

1) Import the CSV file so that they can be compared as "collections" vs "objects"
2) Compare both CSV files using the EMPLOYEE_NUMBER column
3) Output a complete CSV file including all column headers
0
soostibiCommented:
Look at this.
As far as I can understand, you would like to see all object in the resulting table, so compare-object is not your friend here.

Please, customize the paths of your CSVs in the first two lines and in the last line for the output.
$a = import-csv c:\firstcsv.csv
$b = import-csv c:\secondcsv.csv

$h = @{}
$a | %{$h.($_.employee_number) = @($_,"")}
$b | %{
    if(!$h.($_.employee_number)){$h.($_.employee_number) = @("",$_)}
    else{$h.($_.employee_number)[1] = $_}
}
$h.keys | Sort-Object | %{
    New-Object -TypeName PSObject -Property @{
        Employee_Number = $_
        FIRST_NAME_file1 = ($h.$_)[0].First_Name
        LAST_NAME_File1  = ($h.$_)[0].Last_Name
        ZIP_CODE  = ($h.$_)[0].Zip_Code
        FIRST_NAME_File2 = ($h.$_)[1].First_Name
        LAST_NAME_File2  = ($h.$_)[1].Last_Name
        DEPT =  ($h.$_)[1].Dept
        Title = ($h.$_)[1].Title
    }
} | Select-Object -Property Employee_Number, FIRST_NAME_file1, LAST_NAME_File1, FIRST_NAME_File2, LAST_NAME_File2, ZIP_CODE, DEPT, Title |
 export-csv c:\totalcsv.csv -notypeinformation

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

bnditAuthor Commented:
@soostibi
Sorry for the delay in getting back to you...You were right, the Compare-Object was starting to be a pain to accomplish what I wanted to do...for a moment I thought I had figured out how to use it like this

Compare-Object $a $b -Property EMPLOYEE_NUMBER -IncludeEqual -SyncWindow 1000 -PassThru

The above line gave me the entire obj ($a) along with the SideIndicator...from there I still had to do a lot of cleaning...

I tested your solution and I like it much better...much more flexible. With that said, could you help me understand what your code is doing so that I can wrap my head around this concept of passing the entire objects, customized objects...etc? Thanks!!
0
soostibiCommented:
$a = import-csv c:\firstcsv.csv  # import CSVs as Collection of Objects, that means all the rows become objects with properties of the columns of the CSV
$b = import-csv c:\secondcsv.csv  
 
$h = @{}  # initialize an empty hashtable. This is an ideal data type to search and compare, it has label (key) and value pairs
$a | %{$h.($_.employee_number) = @($_,"")}  # fill into the hashtable all the objects of the first CSV, the label (key) will be the employee_number, the value will be an array with two element, the first element is the whole object, the second is an empty string, this will be a placeholder for the matching object from the second CSV if it exists
$b | %{  # do the same with the second CSV
    if(!$h.($_.employee_number)){$h.($_.employee_number) = @("",$_)}  # the difference is, if this is a new account_number that is not already in the hashtable, then create it, with a two-element array, but now the first is empty string, the second is the object
    else{$h.($_.employee_number)[1] = $_}  # if it already exits, put the object as the second element (index 1) into the array.
}  
$h.keys | Sort-Object | %{  # sort all the labels (keys), actually these are the account_numbers
    New-Object -TypeName PSObject -Property @{  # create a new object for every account_number with all the neccessaryproperties
        Employee_Number = $_   # grab the values for these properties from the properties of the hashtable elements
        FIRST_NAME_file1 = ($h.$_)[0].First_Name  
        LAST_NAME_File1  = ($h.$_)[0].Last_Name  
        ZIP_CODE  = ($h.$_)[0].Zip_Code  
        FIRST_NAME_File2 = ($h.$_)[1].First_Name  
        LAST_NAME_File2  = ($h.$_)[1].Last_Name  
        DEPT =  ($h.$_)[1].Dept  
        Title = ($h.$_)[1].Title  
    }  
} | Select-Object -Property Employee_Number, FIRST_NAME_file1, LAST_NAME_File1, FIRST_NAME_File2, LAST_NAME_File2, ZIP_CODE, DEPT, Title |  # the ordering of the columns of the output of the new-object in unpredictable, so I re-select the columns to a sensible order
 export-csv c:\totalcsv.csv -notypeinformation # and export this into a new CSV
0
bnditAuthor Commented:
@soostibi
Great explanation...just one thing...if my CSV files have a lot of columns...say 20+...I'd have to list them all (assuming that I wanted all of them and not just a select few) when I create the new object for every account_number, right?

    New-Object -TypeName PSObject -Property @{  # create a new object for every account_number with all the neccessaryproperties
        Employee_Number = $_   # grab the values for these properties from the properties of the hashtable elements
        FIRST_NAME_file1 = ($h.$_)[0].First_Name  
        LAST_NAME_File1  = ($h.$_)[0].Last_Name  
        ZIP_CODE  = ($h.$_)[0].Zip_Code  
        FIRST_NAME_File2 = ($h.$_)[1].First_Name  
        LAST_NAME_File2  = ($h.$_)[1].Last_Name  
        DEPT =  ($h.$_)[1].Dept  
        Title = ($h.$_)[1].Title  
    }  
0
soostibiCommented:
Here is a generalized version, with any number of columns. The only criteria that there must be one common column: employee_number.

But please, do not ask for an explanation ! :-)
$a = import-csv c:\ee\firstcsv.csv  
$b = import-csv c:\ee\secondcsv.csv  
  
$h = @{}
$props = @()
$a | Get-Member -MemberType properties | Select-Object -ExpandProperty name | %{if($_ -eq "employee_number"){$props += "$_" }else{$props += "$_-File1"}}
$b | Get-Member -MemberType properties | Select-Object -ExpandProperty name | %{if($_ -ne "employee_number"){$props += "$_-File2"}}
$a | %{  
    $o = @{}
    $r = $_
    $r | Get-Member -MemberType properties | Select-Object -ExpandProperty name | %{
        if($_ -eq "employee_number"){$o.$_ = $r.$_}
        else{$o.("$_-File1") = $r.$_}
    }
    $h.($r.employee_number) = $o
}  
$b | %{  
    $o = @{}
    $r = $_
    $r | Get-Member  -MemberType properties | Select-Object -ExpandProperty name | %{
        if($_ -eq "employee_number"){$o.$_ = $r.$_}
        else{$o.("$_-File2") = $r.$_}
    }
    if(!$h.($r.employee_number)){$h.($r.employee_number) = $o}  
    else{
        $o.keys | %{
            if($_ -ne "employee_number"){
                $h.($r.employee_number).$_ = $o.$_
            }
        }
    }  
}  
$h.keys | Sort-Object | %{  
    New-Object -TypeName PSObject -Property $h.$_  
} | Select-Object -Property ("employee_number", ($props | ?{$_ -ne "employee_number"} | Sort-Object) | %{$_} | %{"$_"}) | 
    export-csv c:\ee\totalcsv.csv -notypeinformation -Encoding utf8 -UseCulture

Open in new window

0
bnditAuthor Commented:
@soostibi
haha...fair enough...You've helped me a lot and I increased the points to 500...thank you for your help.
0
bnditAuthor Commented:
Excellent answer!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.