Compare-Object not working if I don't list the properties

Question

I have 2 Excel spreadsheets I am trying to compare:

$OleDbAdapter = New-Object System.Data.OleDb.OleDbDataAdapter “Select * from [Report$]“,”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\FIS-BIC Reporting\Report Output Files\Product-Marketing\TEST_XI\ECM - Pipeline by LOB_04182013_040544.xls;Extended Properties=”"Excel 12.0 Xml;HDR=YES”";”
$RowsReturned = $OleDbAdapter.Fill($DataTable)

$OleDbAdapter2 = New-Object System.Data.OleDb.OleDbDataAdapter “Select * from [Report$]“,”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=S:\FIS-BIC Reporting\Report Output Files\Product-Marketing\ECM - Pipeline by LOB_04182013_074004.xls;Extended Properties=”"Excel 12.0 Xml;HDR=YES”";”
$RowsReturned2 = $OleDbAdapter2.Fill($DataTable2)

Compare-Object $DataTable $DataTable2 

It returns nothing. I know that in the 6th column, they are different. If I specify "-property F6", it does return the difference. Any idea why it doesn't unless I specify the property? The number of columns can vary (though will be the same for each of the files in the comparison), so specifying the properties specifically won't work.

Answer 1

Adi Inbar's helpful answer contains good background information about how Compare-Object works.

However, there is a way to use -Property generically to compare all column values - assuming that both input tables have the same column structure, or that the the tables should only be compared by the first table's columns:

# The original collection.
$coll1 = [pscustomobject] @{ one = '1a'; two = '2a'; three = '3a' },
         [pscustomobject] @{ one = "1b"; two = "2b"; three = '3b' }

# The other collection to compare the original to.
# Note the difference in the 2nd object in column 'two'
$coll2 = [pscustomobject] @{ one = '1a'; two = '2a'; three = '3a' },
         [pscustomobject] @{ one = "1b"; two = "2b!"; three = '3b' }

# PSv3+: Get the array of all property names to compare 
#        from the original collection.
# Note: 
#      * The assumption is that both collections have the same set of 
#        properties (or that the collections should only be compared by
#        the *first* collection's properties).
#      * In PSv2-, use the following instead:
#         $propsToCompare = $coll1[0].psobject.properties | % { $_.name }
$propsToCompare = $coll1[0].psobject.properties.name

# Compare the 2 collections by all property values.
# -PassThru means that any input object in which a difference is found
# is passed through as-is.
Compare-Object $coll1 $coll2 -Property $propsToCompare -PassThru

The above yields:

one two three SideIndicator
--- --- ----- -------------
1b  2b! 3b    =>           
1b  2b  3b    <=           

Note how => tells you that the selected object is exclusive to the right side and vice versa for <=

A caveat is that Compare-Object is slow, because it cannot make assumptions about input data being sorted and therefore has to read and compare both input collections in full.

With sorted input, you can use -SyncWindow <Int32> to speed things up, but that requires advance knowledge of how many items at most can differ between the two input collections following each difference found, and if the -SyncWindow value is too small, spurious differences will be reported.

---

If you compare objects as a whole (no -Property argument), PowerShell uses the following comparison method:

Note:

• Below, LHS refers to an object from the reference collection (-ReferenceObject) and RHS to an object from the difference collection (-DifferenceObject)

• The short of it is: Unless the types involved implement IComparable (which is true for strings and all primitive .NET types (the CLR's number types and [char])) or have custom .ToString() implementations with instance-specific return values from which equality can be inferred, whole-object comparison will not be meaningful, and objects will be treated as equal even when they aren't.

The following logic is implemented in the TryCompare() engine method, which is used whenever two values must be compared, irrespective of context.

• If the LHS is a string, string comparison is performed, which is case-insensitive and culture-invariant by default; the -CaseSensitive and -Culture parameters allow you to change that.

• If both the LHS and the RHS are numbers of (potentially different) .NET primitive types, numeric comparison is performed.

• Otherwise, an attempt is made to convert the RHS is to the type of the LHS and, if the type supports System.IComparable, its .CompareTo() method is called to determine equality.

• Otherwise, the LHS and RHS are compared via the Object.Equals() method that all objects inherit or implement or, if the type implements interface IEquatable<T>, IEquatable<T>.Equals().

  • If System.Object.Equals() is called, and a given type doesn't override it, only value types (structs) composed only of other value-type instances will compare meaningfully; for reference types, only two references to the very same object instance are considered equal.

  • Caveat: As of PowerShell Core 7.1.0-preview.2, only if the .Equals() call returns true is the result used. The reason is that the comparison code is also used for ordering (sorting) values, where determining equality alone is not enough. In the context of Compare-Object, not using a false result is actually inappropriate for types that implement IEquatable<T> (and don't also implement IComparable) - see this GitHub issue.

The remaining logic comes from ObjectCommandComparer.Compare() (which is also used by Get-Unique and Select-Object -Unique):

• If none of the above methods apply, the objects are compared by their .ToString() representations (with the same string-comparison specifics described above).

  • Note that for many types these will not result in meaningful comparison, because the default .ToString() implementation simply returns the full type name (e.g., 'System.IO.FileInfo'), so that all instances of that type compare the same.