How to compare two CSV file using PowerShell

Question

I have a csv file call Full-List.csv that have columns like this

Old_URL    Number

I have another csv file Sub-List.csv that have columns like this

Old_URL  New_URL Title  Owner 

I'm just wondering how can I compare the two csv file using Old_URL column and output to another csv file that look something like this. Basically it'll include a "Number" column now.

New_URL  Title  Owner  Number

$left = Import-Csv 'C:\Users\Downloads\New folder\Full-List.csv'
$right = Import-Csv 'C:\Users\Downloads\New folder\Sub-List.csv'

Compare-Object -ReferenceObject $left -DifferenceObject $right -Property Old_URL -IncludeEqual -ExcludeDifferent | 
    ForEach-Object {
        $iItem = $_
        $ileft = $left.Where({$_.Old_URL -eq $iItem.Old_URL })
        $iright = $right.Where({$_.Old_URL -eq $iItem.Old_URL})
        [pscustomobject]@{
             New_URL = $iright.New_URL
             Number =$ileft.Number
             Title = $iright.Title
             Owner =$iright.Owner
        }
    } | Export-Csv 'C:\Users\Downloads\New folder\Combined.csv' -NoTypeInformation

There are matching Old_URL in both file but some reason my Combined.csv output is empty so any help or suggestion would be really appreciated.

Answer 1

You should really consider using hash table for this, Group-Object -AsHashtable does a great job simplifying this process. Once you have your lookup table you can enumerate the Full-List CSV and output only those objects having a correlated Old_URL value:

$map = Import-Csv path\to\Sub-List.csv | Group-Object Old_URL -AsHashTable -AsString

Import-Csv path\to\Full-List.csv | ForEach-Object {
    if($map.ContainsKey($_.Old_URL)) {
        [pscustomobject]@{
            New_URL = $map[$_.Old_URL].New_URL
            Title   = $map[$_.Old_URL].Title
            Owner   = $map[$_.Old_URL].Owner
            Number  = $_.Number
        }
    }
} | Export-Csv path\to\mergedCsv.csv -NoTypeInformation