Powershell script to compare two excel of keyvalue pair and provide output

Question

Can someone help me with a powershell script which will compare the keyvalue pair in two excel file and provide us the below output

1. Key not present in excel1
2. Key present in both excel 1 and excel2 but values are different

excel 1

key1 value1
key2 value2
key3 value3

excel 2
key2 value4
key3 value3

expected output
key1 - not present in excel2

key2 - present. but the value is value3 instead of value2

Answer 1

This should handle it. I don't know if the messages are what you want, but you can certainly change them, or emit your own PSCustomObject in their place:

$E1 = 'c:\junk\excel1.xlsx'  
$E2 = 'C:\junk\excel2.xlsx'  
$rset = [ordered]@{}  
$dset = [ordered]@{}  
$vset = [ordered]@{}  
  
# check reference set for duplicate keys  
Import-Excel $E1 |  
    ForEach-Object{  
        if ($rset.Contains($_.MyKeys)){  
            Write-Host "Duplicate key in '$E1': " ($_.MyKeys) " skipping key"  
        }  
        else {  
            $rset[$_.MyKeys] = $_.MyValues  
        }  
    }  
# check difference set for duplicate keys and missing keys  
Import-Excel $E2 |  
    ForEach-Object{  
        if ($dset.Contains($_.MyKeys)){  
            Write-Host "Duplicate key in '$E2': " ($_.MyKeys) " skipping key"  
        }  
        else {  
            $dset[$_.MyKeys] = $_.MyValues  
            if (-not $r.Contains($_.MyKeys)){  
                Write-Host "$E1 does not contain key: " ($_.MyKeys)  
            }  
        }  
    }  
# check reference set for missing keys  
$rset.GetEnumerator()|  
    ForEach-Object{  
        if (-not $dset.Contains($_.Key)){  
            Write-Host "$E2 does not contain key: " ($_.Key)  
        }  
    }  
# check for same value is keys present in both sets  
$dset.GetEnumerator()|  
    ForEach-Object{  
        if ($rset.Contains($_.Key)){  
            if ($rset.($_.Key) -ne $_.Value){  
                Write-Host "$E1 does not have same value: " ($_.Key)  
            }  
        }  
    }