Compare 2 XLS

Question

I am trying to compare two XLS file which has DNS name and IP information with three conditions.
1st Condition DNS Name and IP from Old.xls Matches and any record in New.xls.
2nd Condition If 1st condition fails then try to match by IP.
3rd If both 1st and 2nd condition fail then it’s a No Match.

I have written some code and its work fine for most of the records.
However I must improve the logic to first check all the records by IP and DNS combination and then go for IP match if not able to find IP and DNS combination .
As you can see in the screenshot at present it matches 3rd ,6th ,7th Record by IP Only. It should match them by DNS and IP (1st Condition)
I have included the Screenshot of script, xls and output for your reference,
Please advise

----------------------------------

$old = Import-Excel -path C:\test\old.xlsx
$new = Import-Excel -Path C:\test\New.xlsx

$arr = @()
$i = $null

foreach($vm1 in $old)
{
$i = ++$i

foreach($vm2 in $new)
{
if($vm1.'dns' -eq $vm2.'dns' -and $vm1.'ip' -eq $vm2.'ip' )
{

$status  = " DNS and IP match"  
  
$vm1_dns = $vm1.dns  
$vm1_ip  = $vm1.ip  

$vm2_dns = $vm2.dns  
$vm2_ip  = $vm2.ip  
Break  
}  

elseif($vm1.'ip' -eq $vm2.'ip')
{
$status = " IP match"

 $vm1_dns = $vm1.dns  
 $vm1_ip  = $vm1.ip  

 $vm2_dns = $vm2.dns  
 $vm2_ip  = $vm2.ip  
Break  
}  
    
   
else  
{  
 $status  = " No match"  

 $vm1_dns = $vm1.dns  
 $vm1_ip  = $vm1.ip  

 $vm2_dns = ''  
 $vm2_ip  = ''  
}    

}
$obj = New-Object -TypeName PSObject
$obj | Add-Member -Name 'Sno' -MemberType Noteproperty -Value $i
$obj | Add-Member -Name 'Status' -MemberType Noteproperty -Value $status
$obj | Add-Member -Name 'OLD List DNS' -MemberType Noteproperty -Value $Vm1_dns
$obj | Add-Member -Name 'OLD List IP' -MemberType Noteproperty -Value $Vm1_IP

   $obj | Add-Member -Name 'Empty' -MemberType Noteproperty -Value '<<->>'  

   $obj | Add-Member -Name 'New List DNS' -MemberType Noteproperty -Value $Vm2_dns  
   $obj | Add-Member -Name 'New List IP' -MemberType Noteproperty -Value $Vm2_IP  
     
   $arr += $obj  

}
$arr | Out-GridView

Answer 1

See if this works better:

$old = Import-Excel -path C:\junk\old.xlsx
$new = Import-Excel -Path C:\junk\New.xlsx

$arr = @()
$i = 0

foreach ($vm1 in $old) {
    $found = $false
    $i++
    foreach ($vm2 in $new) {
        if ($vm1.'dns' -eq $vm2.'dns' -and $vm1.'ip' -eq $vm2.'ip' ) {
            $status = " DNS and IP match"
            $vm1_dns = $vm1.dns
            $vm1_ip = $vm1.ip
            $vm2_dns = $vm2.dns
            $vm2_ip = $vm2.ip
            $found = $true
            $arr += [PSCustomObject]@{
                Sno = $i
                Status = $Status
                'Old List DNS' = $vm1_dns
                'Old List IP' = $vm1_ip
                Empty = '<<->>'
                'New List DNS' = $vm2_dns
                'New List IP' = $vm2_ip
            }
        }
        elseif ($vm1.'ip' -eq $vm2.'ip') {
            $status = " IP match"
            $vm1_dns = $vm1.dns
            $vm1_ip = $vm1.ip
            $vm2_dns = $vm2.dns
            $vm2_ip = $vm2.ip
            $found = $true
            $arr += [PSCustomObject]@{
                Sno = $i
                Status = $Status
                'Old List DNS' = $vm1_dns
                'Old List IP' = $vm1_ip
                Empty = '<<->>'
                'New List DNS' = $vm2_dns
                'New List IP' = $vm2_ip
            }
        }
    }   # End of NEW
    if (-not $found){
        $arr += [PSCustomObject]@{
            Sno = $i
            Status = " No match"
            'Old List DNS' = $vm1_dns
            'Old List IP' = $vm1_ip
            Empty = ''
            'New List DNS' = ''
            'New List IP' = ''
        }
    }
}
$arr | Out-GridView

I think I would have used hashes. One set of hashes for IP/DNS, and one set for IP, for both old and new data sets (four hashes in all). Then just iterate over the keys in NEW and see if the key is in OLD. You can reverse the comparisons and use OLD to see if the key is in NEW if you need to. It's just set theory!

The "break" in your code was what was mostly causing you problems. You have multiple matches in NEW but each time you hit a match in OLD you jumped to the next OLD row, ignoring the other potential matches.

Answer 2

$old = Import-Excel -path C:\test\old.xlsx
$new = Import-Excel -Path C:\test\New.xlsx

$arr = @()
$i = $null
**$new_dns =$new.dns**

foreach($vm1 in $old)
{
 $i = ++$i

  foreach($vm2 in $new)
  {
   if($vm1.'dns' -eq $vm2.'dns' -and $vm1.'ip' -eq $vm2.'ip' )
    {

    $status  = " DNS and IP match"

    $vm1_dns = $vm1.dns
    $vm1_ip  = $vm1.ip

    $vm2_dns = $vm2.dns
    $vm2_ip  = $vm2.ip
    Break
    }


   **elseif($vm1.'ip' -eq $vm2.'ip' -and $vm1.DNS -notin $new_dns)**
    {

     $status  = " IP match"

     $vm1_dns = $vm1.dns
     $vm1_ip  = $vm1.ip

     $vm2_dns = $vm2.dns
     $vm2_ip  = $vm2.ip
     Break
     }


    else
    {
     $status  = " No match"

     $vm1_dns = $vm1.dns
     $vm1_ip  = $vm1.ip

     $vm2_dns = ''
     $vm2_ip  = ''
    }  
  }
       $obj = New-Object -TypeName PSObject
       $obj | Add-Member -Name 'Sno' -MemberType Noteproperty -Value $i
       $obj | Add-Member -Name 'Status' -MemberType Noteproperty -Value $status
       $obj | Add-Member -Name 'OLD List DNS' -MemberType Noteproperty -Value $Vm1_dns
       $obj | Add-Member -Name 'OLD List IP' -MemberType Noteproperty -Value $Vm1_IP

       $obj | Add-Member -Name 'Empty' -MemberType Noteproperty -Value '<<->>'

       $obj | Add-Member -Name 'New List DNS' -MemberType Noteproperty -Value $Vm2_dns
       $obj | Add-Member -Name 'New List IP' -MemberType Noteproperty -Value $Vm2_IP

       $arr += $obj

}
  $arr | Out-GridView