Tuesday, August 25, 2020

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