Need help: comparing two excel files in powershell

Hi,

I’m working on code to compare two excel files.

File 1 has 2 columns. server name, count

File 2 has 2 columns, servername, count but different column names.

I have to compare file 1 to file 2 such that if servername in file 1 matches with that in file2, compare the column 2 values for a condition is true( condition is file1 count-3 should be equal to file2 count)

$csv1 = Import-Csv .\p1hostcount.csv|Select-Object @{n=“cluster.name”;e={$.“Cluster”}},@{n=“rule_value”;e={$.“hostcount”}} < changing names in 1st file to be same of file2>

$csv2 = Import-Csv .\p1rule.csv

foreach ($csv1 in $csv2)
{

If ( $csv1.‘cluster.name’ -eq $csv2.‘cluster.name’)
{
$targetThreshold = $csv1.‘rule_value’ - 3

If (!($targetThreshold -eq $csv2.‘rule_value’)){
<reporting condition not met here>
Resource = $csv1.‘cluster.name’
Details = “Threshold incorrect (current=$csv2.‘rule_value’,expected=$targetThreshold)”
}

}
}
}

I don’t see any errors but output is not matching to expected result of servers. Please let me know if any recommendations.

 

 

 

For better readability: Formatted code

$csv1 = Import-Csv .\p1hostcount.csv|Select-Object @{n="cluster.name";e={$_."Cluster"}},@{n="rule_value";e={$_."hostcount"}} < changing names in 1st file to be same of file2>
$csv2 = Import-Csv .\p1rule.csv
foreach ($csv1 in $csv2) {
    if ($csv1.'cluster.name' -eq $csv2.'cluster.name') {
        $targetThreshold = $csv1.'rule_value' – 3
        if (!($targetThreshold -eq $csv2.'rule_value')) {
            Resource = $csv1.'cluster.name'
            Details = "Threshold incorrect (current=$csv2.'rule_value',expected=$targetThreshold)"
        }
    }
}

First thing where you go wrong is in the foreach loop. You are overwriting the $csv1 variable with rows in $csv2.

For better understanding, look at it like:

foreach ($row in $csv2) { 
   … 
}

You might need a nested foreach loop, for example:

foreach ($row1 in $csv1) {
    foreach ($row2 in $csv2) {
        if ($row1.'cluster.name' -eq $row2.'cluster.name') {
            Write-Output "We have a match!"
        }
    }
}

Thanks John for the recommendation!

I made the changes to use multiple foreach loops but the script just keeps running for long time(its been more than 30 mins and still running).

In each excel, I have ~300 records. Is there a way we could improvise further?

Hi Divya,

Seems that performance is an issue, I think you can do with a single for loop, but it’s not an easy task.

I came up with the following example:

for ($i = 0; $i -le $csv1.length; $i++) {
    if ($csv2.ServerName.Contains($csv1[$i].ServerName)) {
        $index = [array]::IndexOf($csv2.ServerName, $csv1[$i].ServerName)
        Write-Output "$($csv1[$i].ServerName) found in Csv2 on index $index"
        Write-Output "Rule value for ServerName $($csv1[$i].ServerName) in CSV1: $($csv1[$i].'rule_value')"
        Write-Output "Rule value for ServerName $($csv2[$index].ServerName) in CSV2: $($csv2[$index].'rule_value')"
    }
}

I added the output, so you can verify that the Server names are matching on the correct indexes.

you could use Compare-Object cmdlet, by calculating the count property as well.

$csv1 = Import-Csv .\p1hostcount.csv | Select-Object @{n="cluster.name";e={$_."Cluster"}},@{n="rule_value";e={$_.hostcount -3}}
$csv2 = Import-Csv .\p1rule.csv

#Property one by one
Compare-object -ReferenceObject $csv1 -DifferenceObject $csv2 -Property Name
Compare-object -ReferenceObject $csv1 -DifferenceObject $csv2 -Property count

#Properties together
Compare-object -ReferenceObject $csv1 -DifferenceObject $csv2 -Property Name,Count

Thank you John and Prasoon!

Both helped and compare-object was pretty quick and easy.