Need help: comparing two excel files in powershell

Welcome Forums General PowerShell Q&A Need help: comparing two excel files in powershell

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #122250

    Participant
    Points: 36
    Rank: Member

    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')){

    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.

     

     

     

  • #122276

    Participant
    Points: 111
    Helping Hand
    Rank: Participant

    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!"
            }
        }
    }
    
  • #122307

    Participant
    Points: 36
    Rank: Member

    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?

  • #122325

    Participant
    Points: 111
    Helping Hand
    Rank: Participant

    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.

  • #122388

    Participant
    Points: 815
    Helping Hand
    Rank: Major Contributor

    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
    
  • #122463

    Participant
    Points: 36
    Rank: Member

    Thank you John and Prasoon!

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

     

You must be logged in to reply to this topic.