Comparing two external data sources and combining.

PowerShell Help
1

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

2

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.

3

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.

4

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.

5

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?

6

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.

7

Rob,

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

Thanks!