# Need help: comparing two excel  files in powershell

**URL:** <https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628>\
**Category:** PowerShell Help\
**Created:** [November 6, 2018, 2:15pm UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628 "2018-11-06T14:15:35Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![divya](https://avatars.discourse-cdn.com/v4/letter/d/13edae/32.png) [@divya](https://forums.powershell.org/u/divya)\
**Post date:** [November 6, 2018, 2:15pm UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628/1 "2018-11-06T14:15:35Z")

</div>

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.

&nbsp;

&nbsp;

&nbsp;

---

<div class="post-metadata">

**Author:** ![jseerden](https://avatars.discourse-cdn.com/v4/letter/j/ce7236/32.png) [@jseerden](https://forums.powershell.org/u/jseerden)\
**Post date:** [November 6, 2018, 3:25pm UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628/2 "2018-11-06T15:25:48Z")

</div>

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!"
        }
    }
}
```

---

<div class="post-metadata">

**Author:** ![divya](https://avatars.discourse-cdn.com/v4/letter/d/13edae/32.png) [@divya](https://forums.powershell.org/u/divya)\
**Post date:** [November 6, 2018, 4:40pm UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628/3 "2018-11-06T16:40:51Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jseerden](https://avatars.discourse-cdn.com/v4/letter/j/ce7236/32.png) [@jseerden](https://forums.powershell.org/u/jseerden)\
**Post date:** [November 6, 2018, 5:06pm UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628/4 "2018-11-06T17:06:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![kvprasoon](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/kvprasoon/32/4449_2.png) [@kvprasoon](https://forums.powershell.org/u/kvprasoon)\
**Post date:** [November 7, 2018, 12:07am UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628/5 "2018-11-07T00:07:28Z")

</div>

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
```

---

<div class="post-metadata">

**Author:** ![divya](https://avatars.discourse-cdn.com/v4/letter/d/13edae/32.png) [@divya](https://forums.powershell.org/u/divya)\
**Post date:** [November 7, 2018, 9:49am UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628/6 "2018-11-07T09:49:49Z")

</div>

Thank you John and Prasoon!

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

&nbsp;

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:34pm UTC](https://forums.powershell.org/t/need-help-comparing-two-excel-files-in-powershell/11628/7 "2024-05-16T20:34:48Z")

</div>

Closed on May 16, 2024, 8:34pm UTC


