powershell: compare specific columns of CSV files and return entire row of differences

Question

I have 2 CSV files I'd like to compare. They both have multiple columns of different data but they also both have a column with IP addresses. Call them $log1 and $log2

I am trying to compare the files. If an IP from $log1 is found in $log2 I would like an output file that has the entire row of data from the match of $log2...

When I use:

Compare-Object -Property 'IP address' -ReferenceObject $log1 -DifferenceObject $log2

It returns only the 'IP address' column and the SideIndicator.

I think I'm barking up the wrong tree here, can anyone offer some advice?

`$log2|Where-Object 'IP address' -in $log1.'IP address'` – user4003407 Oct 29 '15 at 14:41 — user4003407, Oct 29 '15 at 14:41
Try adding the `-PassThru` flag to your command. – Dave Sexton Oct 29 '15 at 14:45 — Dave Sexton, Oct 29 '15 at 14:45

score 0 · Answer 1 · answered Oct 29 '15 at 15:06

0

I would try another approach :

$log1,$log2 | group-object -Property 'IP address' | where {$_.count -eq 2}

In the result you will find a group property with the two lines.

answered Oct 29 '15 at 15:06

JPBlanc

63,198
12
118
155

score 0 · Answer 2 · answered Oct 29 '15 at 15:49

0

"Try adding the -PassThru flag to your command." - Dave Sexton

This works. I exported the results to CSV and sorted by the SideIndicator when I opened the file (don't think you can get PS to sort by SideIndicator).

Thanks Dave.

There's probably more ways to accomplish this, as noted by others but this achieved my goal.

answered Oct 29 '15 at 15:49

Davey

1
1
2

*don't think you can get PS to sort by SideIndicator* Does `sort SideIndicator` not work for you? – user4003407 Oct 29 '15 at 16:00

score 0 · Answer 3 · answered Oct 29 '15 at 16:32

This script will compare your both csv files and writhe output for each double ip address found.

#import your csv files
$csv1 = Import-Csv "C:\Users\Admin\Desktop\csv\csv1.csv"
$csv2 = Import-Csv "C:\Users\Admin\Desktop\csv\csv2.csv"

#Compare both csv files (.ip corresponds to your column name for your ip address in the csv file )
$compare = Compare-Object $csv1.ip $csv2.ip -IncludeEqual | ? {$_.SideIndicator -eq "=="}

if ($compare) {

    foreach ($ip in $compare.InputObject) {
    Write-Output "IP $ip exist in both csv files"
    }

}
Else 
    {
        Write-Output "Double ip not found"
    }

powershell: compare specific columns of CSV files and return entire row of differences

3 Answers3