I don’t know of a SQL-like join function in powershell, which in no way means that there isn’t one. 
I’m hoping that one of the gurus here will say that this post was unnecessary and let us know of a good and easy way to do it.
I would have liked to use the LINQ Join method, but calling generic methods (such as [System.Linq.Enumerable]::Join) isn’t always the easiest in PowerShell, as far as my meager understanding of the language takes me. However, performing your join should be pretty simple to do in code.
I’m assuming your files are comma-separated. If they are tab-separated, just add a Delimiter parameter to the Import-Csv and Export-Csv calls, providing the tab character for delimiter character. I’m also assuming that all user names are unique.
If you are certain that all users exist in both files and only once in each file, the following simple solution could probably work for your scenario:
function Join-UserData
{
PARAM (
$UserData,
$AppendData
)
# If you are certain the arrays are already sorted in the same way, this step is unnecessary
$sortedUserData = $UserData | Sort DisplayName
$sortedAppendData = $AppendData | Sort User
# Just iterate through the array and copy the Total Size (MB) property to the user object
for($index = $sortedUserData.Length - 1; $index -ge 0; $index–)
{
$user = $sortedUserData[$index]
$appendDataItem = $sortedAppendData[$index]
Add-Member -InputObject $user -MemberType NoteProperty -Name ‘Total Size (MB)’ -Value $appendDataItem.‘Total Size (MB)’
}
Write-Output $sortedUserData
}
$userData = Import-Csv userData.csv
$appendData = Import-Csv appendData.csv
Join-UserData -UserData $userData -AppendData $appendData | Export-Csv output.csv
If you are not certain that all users exist in the AppendData file, it needs some alteration. Just as a quick exercise for me, I wrote a version which will copy all properties (except the key property) from the appendData to the user data and also allow you to provide the name of the key property as a parameter (which takes it closer to becoming a more reusable function). That version comes next:
function Join-UserData
{
PARAM (
$UserData,
$AppendData,
$UserDataKeyPropertyName,
$AppendDataKeyPropertyName
)
# Sort the arrays so we only need to iterate through the items once in the code later
# If you are certain your data is already sorted, this isn’t necessary
$sortedUserData = $UserData | Sort $UserDataKeyPropertyName
$sortedAppendData = $AppendData | Sort $AppendDataKeyPropertyName
$userDataLength = $sortedUserData.Length
$appendDataLength = $sortedAppendData.Length
$appendDataIndex = 0;
$appendDataItem = $sortedAppendData[$appendDataIndex];
for($userIndex = 0; $userIndex -lt $userDataLength; $userIndex++)
{
$user = $sortedUserData[$userIndex];
while(($user.“$UserDataKeyPropertyName” -gt $appendDataItem.“$AppendDataKeyPropertyName”) -AND ($appendDataIndex -lt $appendDataLength))
{
# As long as the users key property value is greater than the append data key property value
# we can just ignore the appendDataItem, since no matching user will be found.
# This is only valid as long as we do an outer left join, which I have assumed
$appendDataIndex++
$appendDataItem = $sortedAppendData[$appendDataIndex];
}
if ($appendDataIndex -ge $appendDataLength)
{
# If we have already looked through the whole array with append data we won’t find any more data to append, so we’re finished
break
}
if ($user.“$UserDataKeyPropertyName” -ceq $appendDataItem.“$AppendDataKeyPropertyName”)
{
# We have matching keys, so identify all NoteProperties other than the key property and copy those
# This will throw an exception if there are duplicate property names in the user object and the appendItem.
$properties = $appendDataItem | gm -MemberType NoteProperty
foreach ($property in $properties)
{
if (-Not($property.Name -eq $AppendDataKeyPropertyName))
{
Add-Member -InputObject $user -MemberType NoteProperty -Name $property.Name -Value $appendDataItem.“$($property.Name)”
}
}
}
}
Write-Output $sortedUserData
}
$userData = Import-Csv userData.csv
$appendData = Import-Csv appendData.csv
Join-UserData -UserData $userData -AppendData $appendData -UserDataKeyPropertyName DisplayName -AppendDataKeyPropertyName User | Export-Csv output.csv
[Edit: Formatting issues, even though I avoided backticks Had to remove all empty lines, which unfortunately might make it harder to read according to me.]
!