powershell: pull data from two csv files, output csv file with matching emails and add id number matching to that email in next column

Question

So I have two csv files from different sources. They each have an employee id column csv1 has "employeeid" column with employeeid numbers csv2 has "employeeid" column with no employeeid numbers

both files also have emailaddress field

I want to match the emailaddresses between both csv files and then give me an output of emailaddresses matched column and a column showing what employeeid from csv1 corresponds with the emailaddress match

does that make sense?

Here is what i have so far and it seems kind of janky and i confused myself lol

$Path = C:\Powershell
$userhr = import-csv -path C:\userhr.csv
$userdata = import-csv -path c:\userdata.csv
$useroutput = @()

For Each ($name in $userhr)
{
$usermatch = $userdata | where {_$.Username -eq $name.usernames}
 if ($usermatch)
{
$useroutput += New-Object PsObject -Property @{Username =$name.usernames,employeeid= 
$usermatch.employeeid.employeeid =$usermatch.employeeid}
{
else {
$useroutput += new-object Psobject -property @{username$name.usernames;employeeid"NA";employeeid="NA"}

 $useroutput | export-csv c:\outfile.csv

i kinda hashed together some of my knowledge and some of what i found online

but kinda wanna start fresh and see how others would solve this issue

Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[Module](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `import-csv C:\userhr.csv |Update-Object (import-csv C:\userdata.csv) -On UserNames -Eq UserName` — iRon, Jun 11 '21 at 10:37
As a side note: [try avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) as it is exponentially expensive. — iRon, Jun 11 '21 at 10:59
@iRon I have your answer of your 2nd comment in bookmarks ;) I had the misconception that `generic.list` would\should perform addition faster than variable assignment to the loops and after testing I saw no difference. Awesome answer! — Santiago Squarzon, Jun 11 '21 at 16:39

Santiago Squarzon · Answer 1 · 2021-06-10T14:01:50.627

I added some comments to help you with the thought process:

$csvWithIDs = Import-Csv -Path C:\userhr.csv
$csvWithoutIDs = Import-Csv -Path c:\userdata.csv

# Create a new hashtable which is perfect for your need
# Keys will be the emailAddresses which we now exists in
# both CSV, Values of each Key will be the employeeID which
# we want to use to populate the CSV without IDs
$emailMap = @{}

foreach($line in $csvWithIDs)
{
    if(-not $emailMap.ContainsKey($line.emailAddress))
    {
        $emailMap.Add($line.emailAddress,$line.employeeID)
        # Note: emailAddress is supposed to be the exact name
        # of the CSV Header, if it's not, change "employeeID" for the
        # header name
    }
}

foreach($line in $csvWithoutIDs)
{
    # Same here, header name on this CSV should also
    # be updated with exact header name
    if($emailMap.ContainsKey($line.emailAddress))
    {
        
        $foundID = $emailMap[$line.emailAddress]

        # If this mail is in the emailMap return it's
        # emailAddress and employeeID to the PS Host
        "{0} - {1}" -f $line.emailAddress,$foundID
        # This will return a string if you want an object
        # instead, remove above line and use this:
        [pscustomobject]@{
            EmailAddress = $line.emailAddress
            EmployeeID = $foundID
        }

        # And if you want to update the employeeID value on the
        # Csv, This is how you do it
        $line.employeeID = $foundID
    }
}

# Now the Csv without IDs should be populated with the employeeIDs
# you can export it again
$csvWithoutIDs | Export-Csv "x:\path\to\csv.csv" -NoTypeInformation

powershell: pull data from two csv files, output csv file with matching emails and add id number matching to that email in next column

1 Answers1