Created
July 7, 2017 17:09
-
-
Save anonymous/cd91fe21e154aed06c9576740c3ac95b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
.SYNOPSIS | |
Compares two CSV files using a common field. | |
.DESCRIPTION | |
Compares two CSV files using an index, or common field to flag and find rows with that matching field instead of comparing the files row by row, sequentially. | |
This will typically be used with files that have matching syntax and output from the same application or service. The oldest version of the two files being compared | |
should be Csv1. The newest version of the files being compared should be Csv2. | |
.PARAMETER Csv1FilePath | |
The path of the first Csv file. This should read the same as the expected parameter value for 'Import-Csv -Path'. | |
.PARAMETER Csv2FilePath | |
The path of the second Csv file. This should read the same as the expected parameter value for 'Import-Csv -Path'. | |
.PARAMETER CsvIndexField | |
This is a string used in a field that both files have in common. This will be used to find corresponding rows in both files, as the files are not compared row by row, sequentially. | |
.PARAMETER CsvSharedField1 | |
Another field that both files share. The values in these fields will be compared whenever a matching row is found between both files using the parameter value of CsvIndexField. | |
.EXAMPLE | |
PS C:\> Compare-TwoCsvFiles -Csv1FilePath 'C:\File1.Csv' -Csv2FilePath 'C:\File2.Csv' -CsvIndexField 'Username' -CsvSharedField1 'Birthday' | |
PS C:\> $DeltaHashTable | |
Name Value | |
---- ----- | |
Bob { Birthday } | |
Jim { Birthday } | |
PS C:\> $DeltaHashTable.('Bob') | |
Name Value | |
---- ----- | |
Birthday 4/4/84 | |
This example shows how to call the function and explore its output. | |
.INPUTS | |
System.String | |
.OUTPUTS | |
System.Collections.Hashtable | |
.LINK | |
about_functions_advanced | |
.LINK | |
about_comment_based_help | |
.LINK | |
about_functions_advanced_parameters | |
.LINK | |
about_functions_advanced_methods | |
#> | |
function Compare-TwoCsvFiles { | |
[CmdletBinding()] | |
param( | |
[Parameter(Position=0, Mandatory = $True)] | |
[System.String] | |
$Csv1FilePath, | |
[Parameter(Position = 1, Mandatory = $True)] | |
[System.String]$Csv2FilePath, | |
[Parameter(Position = 2, Mandatory = $True)] | |
[System.String]$CsvIndexField, | |
[Parameter(Position = 3)] | |
[System.String]$CsvSharedField1, | |
[Parameter(Position = 4)] | |
[System.String]$CsvSharedField2, | |
[Parameter(Position = 5)] | |
[System.String]$CsvSharedField3, | |
[Parameter(Position = 6)] | |
[System.String]$CsvSharedField4, | |
[Parameter(Position = 7)] | |
[System.String]$CsvSharedField5 | |
) | |
BEGIN | |
{ | |
$Csv1 = Import-Csv -Path $Csv1FilePath | |
$Csv2 = Import-Csv -Path $Csv2FilePath | |
$Csv1RowCount = $Csv1.Count | |
$Csv2RowCount = $Csv2.Count | |
If ($Csv1RowCount -gt $Csv2RowCount) | |
{ $RowDeltaCount = $Csv1RowCount - $Csv2RowCount } | |
Else { $RowDeltaCount = $Csv2RowCount - $Csv1RowCount } | |
[System.Collections.ArrayList]$Csv1IndexField = $Csv1.$CsvIndexField | |
[System.Collections.ArrayList]$Csv2IndexField = $Csv2.$CsvIndexField | |
} | |
PROCESS | |
{ | |
$CsvIndexFieldComparison = Compare-Object -ReferenceObject $Csv1IndexField -DifferenceObject $Csv2IndexField -IncludeEqual | |
$CsvSharedIndexFields = $CsvIndexFieldComparison | Where-Object { $_.SideIndicator -eq '==' } | Select-Object -ExpandProperty InputObject | |
$Dataset = @{ } | |
ForEach ($Row in $Csv1) { $Dataset[$Row.$CsvIndexField] = @{ 'Csv1' = $Row } } | |
ForEach ($Row in $Csv2) { If ($Dataset[$Row.$CsvIndexField]) { $Dataset[$Row.$CsvIndexField].Csv2 = $Row } Else { $Dataset[$Row.$CsvIndexField] = @{ 'Csv2' = $Row } } } | |
$DeltaHashTable = @{ } | |
ForEach ($Item in $Dataset.Keys) | |
{ | |
If ($Dataset.($Item).Csv1 -and $Dataset.($Item).Csv2) | |
{ | |
$Csv1HashCode = ($Dataset.$Item.Csv1 | Out-String).GetHashCode() | |
$Csv2HashCode = ($Dataset.$Item.Csv2 | Out-String).GetHashCode() | |
If($Csv1HashCode -ne $Csv2HashCode) | |
{ | |
If($CsvSharedField1){ If ($Dataset.$Item.Csv1.$CsvSharedField1.GetHashCode() -ne $Dataset.$User.Csv2.$CsvSharedField1.GetHashCode()) { $DeltaHashTable += @{ $Item = @{ $CsvSharedField1 = $($Dataset.$Item.Csv2.$CsvSharedField1) } } } } | |
If($CsvSharedField2){ If ($Dataset.$Item.Csv1.$CsvSharedField2.GetHashCode() -ne $Dataset.$User.Csv2.$CsvSharedField2.GetHashCode()) { $DeltaHashTable += @{ $Item = @{ $CsvSharedField2 = $($Dataset.$Item.Csv2.$CsvSharedField2) } } } } | |
If($CsvSharedField3){ If ($Dataset.$Item.Csv1.$CsvSharedField3.GetHashCode() -ne $Dataset.$User.Csv2.$CsvSharedField3.GetHashCode()) { $DeltaHashTable += @{ $Item = @{ $CsvSharedField3 = $($Dataset.$Item.Csv2.$CsvSharedField3) } } } } | |
If($CsvSharedField4){ If ($Dataset.$Item.Csv1.$CsvSharedField4.GetHashCode() -ne $Dataset.$User.Csv2.$CsvSharedField4.GetHashCode()) { $DeltaHashTable += @{ $Item = @{ $CsvSharedField4 = $($Dataset.$Item.Csv2.$CsvSharedField3) } } } } | |
If($CsvSharedFIeld5){ If ($Dataset.$Item.Csv1.$CsvSharedField5.GetHashCode() -ne $Dataset.$User.Csv2.$CsvSharedField5.GetHashCode()) { $DeltaHashTable += @{ $Item = @{ $CsvSharedField5 = $($Dataset.$Item.Csv2.$CsvSharedField5) } } } } | |
} | |
} | |
} | |
} | |
END | |
{ | |
$DeltaHashTable | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment