Powershell conundrum: how to compare to CSVs and pull matching data from each?

Question

I've got a CSV file with a list of names in a "name" column. In a second CSV I have a list of names in a "name" column, and also their employee IDs in an "employeeid" column.

My desired output is this: take the "name" column in CSV1, compare it to the "name" column in CSV2, and wherever there's a match, take the name from the "name" column of CSV1 and the matching employee ID in CSV2, and create a new CSV3 with a "name" column and corresponding "employeeid" column.

Here is an image describing my question

I've started playing with import-csv to pull each CSV in as a variable, and also tinkered with piping that with select headers, but I don't understand the logic necessary to take username column matches between CSV1 and CSV2 and then combine that with employeeID from CSV2.

Thanks in advance for any guidance you can provide.

Brian

-- Updated 3/19

$csv1 = import-csv -Path "C:\csv1.csv" | select-object -ExpandProperty 'accountname' | Sort-Object | where {$_ -ne ""}
$csv2 = import-csv -Path "C:\csv2.csv" | select 'accountname','employeeid'

$accountNamesFromCSV2 = $csv2 | select-object -ExpandProperty accountname 
$compareTheTWo = Compare-Object -ReferenceObject $csv1 -DifferenceObject $accountNamesFromCSV2 -ExcludeDifferent -IncludeEqual -passThru

You have no code yet but here is the solution at a high level: 1. Read CSV1 into powershell and create CSV1simple using only the name column 2. Use compare-object to compare CSV1Simple to CSV2 3. Use any matches to update CSV2 from CSV1 — Jimbo
My approach to this would be to load CSV1 into an array, then match the name field from each record in CSV2 against CSV1, writing out the name and id fields into CSV3 if they match. The -contains operator should be useful here, and I suspect that the only cmdlets needed will be Where-Object and Select-Object (and the CSV cmdlets). — Jeff Zeitlin

EBGreen EBGreen · Accepted Answer · 2018-03-16T18:28:50

It's friday so I'll be nice. To do it at the prompt:

$csv2 = import-csv .\bar.csv; Import-CSV .\foo.csv | %{$name1 = $_.name;$id = ($csv2 | ?{$_.Name -eq $name1}).EmployeeID; Add-Member -InputObject $_ -MemberType NoteProperty -Name EmployeeID -Value $id; $_} | select Name, EmployeeID | Export-CSV .\result.csv

Or, a little more human friendly version in a script:

$csv2 = Import-CSV .\bar.csv
$result = New-Object System.Collection.ArrayList
foreach($user in (Import-CSV .\foo.csv)){
    $id = ($csv2 | Where-Object{$_.Name -eq $user.Name).EmplyeeID
    Add-Member -InputObject $user -MemberType NoteProperty -Name EmployeeID -Value $id
    $result.Add($user) | Out-Null
}
$result | Export-CSV .\result.csv -NoTypeInformation

Powershell conundrum: how to compare to CSVs and pull matching data from each?

1 Answers