Comparing two external data sources and combining.

So, there are a couple of things that I’m struggling with here.

I have a list of user ids. I also have a list of AD users. I’m comparing the two and looking for matches.

CSV 1

Filename,UserID
something.txt,user1

CSV 2

UserID,FirstName,LastName,Email
user1,User, 1, user1@email.com

I want to compare the UserID column in csv 1 with the userid column in csv 2 (or vice versa).

My end result would look like this…

UserID,FirstName,LastName,Email,Match
user1,user,1,user1@email.com,TRUE

Where the Match column would be the $_.sideindicator translated into a Boolean.

Here is what I have and it seems to “look” like it is doing what I want but some information is not making it too the final product.

$yearbook = Get-ChildItem “\servername\highschool\yearbook” -Force | where {$.PSIsContainer -eq $false -and $.Name -like “*.jpg”}
$pics = @()

Foreach ($pic in $yearbook)
{
$obj = New-Object PSObject
$obj | Add-Member -MemberType NoteProperty -Name FileName -Value $pic.Name
$obj | Add-Member -MemberType NoteProperty -Name UserID -Value $pic.BaseName
$pics += $obj
}

$Pics | export-csv -Path “$PSScriptRoot\temppics.csv”
$CSV = “$PSScriptRoot\temppics.csv”

$userTable = @{}

filter UpdateUserTable
{
$userTable[$.SamAccountName] = [pscustomobject] @{
FirstName = $
.FirstName
LastName = $.LastName
Email = $
.Email
}
}

$userTable = Get-QADUser -SizeLimit 0 | Where { ($_.type -eq “user”) | UpdateUserTable

$userdump = @()

Foreach ($user in $userTable)
{
$obj = New-Object PSObject
$obj | Add-Member -MemberType NoteProperty -Name UserID -Value $user.SamaccountName
$obj | Add-Member -MemberType NoteProperty -Name ‘First Name’ -Value $user.FirstName
$obj | Add-Member -MemberType NoteProperty -Name ‘Last Name’ -Value $user.LastName
$obj | Add-Member -MemberType NoteProperty -Name ‘E-Mail’ -Value $user.Email
$userdump += $obj
}
$userdump | export-csv -Path “$PSScriptRoot\tempad.csv”

$file1 = import-csv -Path “$psscriptroot\tempad.csv”
$file2 = import-csv -Path “$psscriptroot\temppics.csv”
$compare = Compare-Object $file1 $file2 -property UserID -IncludeEqual
$compare | Export-Csv -Path “$psscriptroot\final.csv” -NoTypeInformation

$csv= ‘c:\somefolder\master.csv’

$userTable = @{}

filter UpdateObject
{
$csvEntry = $_
$userEntry = $userTable[$csvEntry.UserID]

[pscustomobject] @{
    UserID        = $csvEntry.UserID
    FirstName     = $user.FirstName
    LastName      = $user.LastName
    Email         = $user.Email
    Bool = $record.SideIndicator
}

}

Import-Csv “$psscriptroot\final.csv” |
UpdateObject |
Export-Csv -Path “$psscriptroot\final1.csv” -NoTypeInformation

I tested this by comparing two test csv files. The problem is that final.csv only contains the results of the compare operation:

"UserID","SideIndicator"
"bob1","=="
"willow1","=="
"buffy1","=>"

The input file to the filter (final.csv) doesn’t contain the properties that you’re trying to use for the object creation. You will need to pull this information from $file2. I updated the filter as shown below:

filter UpdateObject
 {
 $csvEntry = $_
 $userEntry = $userTable[$csvEntry.UserID]

[pscustomobject] @{
 UserID = $csvEntry.UserID
 FirstName = $file2 | Where-Object {$_.userid -eq $csvEntry.userID} | Select-Object -ExpandProperty firstname
 LastName = $file2 | Where-Object {$_.userid -eq $csvEntry.userID} | Select-Object -ExpandProperty LastName
 Email = $file2 | Where-Object {$_.userid -eq $csvEntry.userID} | Select-Object -ExpandProperty Email
 Vinnie = $_.SideIndicator -eq '=='
 }
 }

final1.csv now looks like this:

"UserID","FirstName","LastName","Email","Vinnie"
"bob1","bob","ajob","bob.ajob@contoso.com","True"
"willow1","willow","rosenberg","willow.rosenberg@contoso.com","True"
"buffy1","buffy","summers","buffy.summers@contoso.com","False"

edit: updated the filter and output for final1.csv as you wanted a boolean for Vinnie (why is this column called Vinnie?). This edit will give you a true or false value for the side indicator.

Matt,

I see where you are going but unfortunately, I’m not seeing the same results. Once the “final.csv” is called back in, each object is a PSObject so $csvEntry.UserID ends up looking like

@{username, filename, =>}.Username

In the console and at the end of this, my final1 csv file is empty because it can’t match $PSObject with $_.userid because it doesn’t make sense.

Just to reiterate, here is my goal.

  1. GCI on a directory full of yearbook photos that are named with user ids. (bgates.jpg for example)
  2. Pull all AD users down into a filter or csv or something
  3. Match the USERID from Get-ADUSER to the USERID in the GCI csv.
  4. Output one CSV with Userinfo (SamAccountName, first, last, email) and I want to do something with the sideindicator like

If ($.SideIndicator -like “”)
{
$picstatus = No user for picture
}
If ($
.SideIndicator -like “==”)
{
$picstatus = Matched
}

My last CSV needs to look like this…

UserID, FirstName, LastName, Email, Picture
bgates, Bill, Gates, bgates@contoso.com,Matched
djones, Don, Jones, djones@contoso.biz, No Picture For user

I think updating the object is messing me up but i’m really lost here.

The test CSV files I created used the format described at the top of your post. If you use my filter you get the expected output which implies that everything from

$file1 = import-csv -Path “$psscriptroot\tempad.csv”
onwards is OK when using my filter.

Do your two files tempad.csv and temppics.csv look OK i.e. as described at the top of your post?

Your end goal is to basically see if a picture exists for each user in AD? Rather than doing exports to CSV’s, why not try something like this:

$results = Get-QADUser -SizeLimit 0 | 
    Select SamAccountName, 
           FirstName, 
           LastName, 
           Email,
           @{Name="PictureExists";Expression={Test-Path -Path ("\\servername\highschool\yearbook\{0}.jpg" -f $_.SamAccountName)}}

$results | Export-CSV C:\results.csv -NoTypeInformation

Test-Path will return a Boolean value of True or False if the file exists.

Rob,

Thats great and exactly what I love about Powershell.org. We all approach these things differently and learn everyday!

Thanks!