Home > Software engineering >  Compare 2 CSV files using powershell
Compare 2 CSV files using powershell

Time:02-10

I am comparing 2 csv files against IP address.

Below is the code, but it is not giving me the correct report. there are IP addresses present in $CSV2 but it is not showing

$CSV1 = Import-Csv -Path "E:\IP_Details.csv"
$CSV2 = Import-Csv -Path "E:\IP_Details_1.csv"

$Count = $CSV1.Count
$Results = For ($i = 0; $i -lt $Count; $i  ) {

$IP_one = $CSV1[$i].'IP adress'.Trim()
$IP_two = $CSV2[$i].'IP adress'.Trim()

    If ($CSV1[$i].'IP adress' -eq $CSV2[$i].'IP adress') {
        $Match = "Match"
    } 
    Else {
        $Match = "Not Match"
    }
    [PSCustomObject]@{
        
        Value = $CSV1[$i].'IP adress'
        IPName = $CSV1[$i].IPName
        Results = $Match
    }
}
$Results | Export-Csv -Path "E:\File.csv" -NoTypeInformation

Please let me know what is wrong in the code

Got the below lines which is working fine

$file1 = import-csv -Path "E:\InfoBlox_Used_IP_Details.csv" 
$file2 = import-csv -Path "E:\CMDB_IP_Details.csv" 
Compare-Object $file1 $file2 -property 'IP adress' -IncludeEqual | Export-Csv -Path e:\testmantest2.csv

but it is giving me output like

"IP adress","SideIndicator"
"10.15.9.8","=="
"10.15.9.6","=="
"10.15.9.14","=="
"10.12.21.10","<="

but I need output like below where == should be match and >= is not match

"IP adress","IP Name","Status"
"10.15.9.8","one","match"
"10.15.9.6","two","match"
"10.15.9.14","three","match"
"10.12.21.10","four","Not macth"

Can you please let me know how can I do that

CodePudding user response:

Here is how you can approach the problem, since you're only interested in knowing if an IP in CSV1 exists in CSV2, using a Hashset should be more efficient than Compare-Object. As for what you're doing wrong, on your initial code, you're running a line-by-line comparison hence even if an IP exists in both Csvs, if they're not on the same line / row your condition will be $false.

$CSV1 = Import-Csv -Path 'C:\path\to\csv1.csv'
$reference = [System.Collections.Generic.HashSet[string]]::new(
    [string[]](Import-Csv -Path 'C:\path\to\csv2.csv').'IP address'.ForEach('Trim')
)

$results = foreach($line in $Csv1)
{
    $ip = $line.'Ip address'.Trim()
    [PSCustomObject]@{
        Value   = $ip
        IPName  = $line.IPName
        Results = ('Not Match', 'Match')[$reference.Contains($ip)]
    }
}

$results | Export-Csv -Path "E:\File.csv" -NoTypeInformation
  • Related