Compare two XLS file (without compare-object)

[KS_10]

I am trying to compare two XLSX file with three conditions. which has DNS name and IP information .

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 code, xlsx files and output of script 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

moderator edit by wila: Moved to PowerCLI discussions. Not even sure it should go there as it is a powershell question, not a VMware one.

[LucD · Accepted solution]

This is not really a PowerCLI question, more of a general coding issue.

In any case, try something like this

$old = Import-Excel -Path C:\Temp\old.xlsx
$new = Import-Excel -Path C:\Temp\new.xlsx

$matchIPDNS = @()
$noMatchOld = @()
$noMatchNew = @()

foreach ($vm1 in $old) {
    $found = $false
    foreach ($vm2 in $new) {
        if ($vm1.'dns' -eq $vm2.'dns' -and $vm1.'ip' -eq $vm2.'ip' ) {
            $matchIPDNS += New-Object -TypeName 'PSObject' -Property @{
                Sno            = $i
                Status         = 'DNS and IP match' 
                'OLD List IP'  = $Vm1.IP
                'OLD List DNS' = $Vm1.DNS
                'New List IP'  = $Vm2.IP
                'New List DNS' = $Vm2.DNS
            }
            $found = $true
        } else {
            $noMatchNew += $vm2
        }
    }
    if (-not $found) {
        $noMatchOld += $vm1
    }
}

$matchIP = @()
$noIPMatchOld = @()

foreach ($vm1 in ($noMatchOld | Sort-Object -Property IP, DNS -Unique)) {
    $found = $false    
    foreach ($vm2 in ($noMatchNew | Sort-Object -Property IP, DNS -Unique)) {
        if ($vm1.IP -eq $vm2.IP) {
            $matchIP += New-Object -TypeName 'PSObject' -Property @{
                Sno            = $i
                Status         = 'IP match' 
                'OLD List IP'  = $Vm1.IP
                'OLD List DNS' = $Vm1.DNS
                'New List IP'  = $Vm2.IP
                'New List DNS' = $Vm2.DNS
            }
            $found = $true
        }
    }
    if (-not $found) {
        $noIPMatchOld += New-Object -TypeName 'PSObject' -Property @{
            Status         = 'No match' 
            'OLD List IP'  = $Vm1.IP
            'OLD List DNS' = $Vm1.DNS
            'New List IP'  = ''
            'New List DNS' = ''
        }
    }
}
$arr = $matchIPDNS + $matchIP + $noIPMatchOld
$arr | select 'OLD List IP', 'OLD List DNS', Status, 'New List IP', 'New List DNS' | Out-GridView

[KS_10]

Hey LucD Thanks for the quick reply. You Code works like charm.. thanks a ton

I also made some changes to my code in the weekend and then its working as per my expectation
I am very new to Powershell, so I am sure my code must have some bugs... 
Anyway thanks a ton again, I learned some new things and new techniques from your code
-------------------------

$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