How to Join Two CSV Files on a Key

The following loop shows how to extend a CSV file adding extra fields that can be filled in in a loop by looking up the extra data in a second CSV.

Import-Csv employees.csv | 
    select *,extra1,extra2,extra3 |
    ForEach-Object{
        # get manager for current employee
        # assign fields
    } |
    Export-Csv newfile.csv

First we have import the lookup file:

$managers = Import-Csv managers.csv

The technique to do the lookup is simple:

$item = $lookup | Where-Object{ $_.Key -eq $emp.Key }

Now just assign the elements.

Full Example:

$managers = Import-Csv managers.csv
Import-Csv employees.csv |
    Select-Object *,extra1,extra2,extra3 |
    ForEach-Object{
        $empkey = $_.Key
        # get manager for current employee
        $manager = $lookup | Where-Object{ $_.Key -eq $empkey }
        # assign fields
        $_.extra1 = $manager.Extra1
        $_.extra2 = $manager.Extra2
        $_.extra3 = $manager.Extra3
    } |
    Export-Csv newfile.csv