Comparing two external data sources and combining.

This topic contains 6 replies, has 3 voices, and was last updated by  Paul Goffar 2 years, 11 months ago.

  • Author
  • #27279

    Paul Goffar

    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


    CSV 2

    user1,User, 1,

    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...


    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

  • #27280

    Matt Bloomfield

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


    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:


    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.

  • #27287

    Paul Goffar


    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.

  • #27288

    Paul Goffar

    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,,Matched
    djones, Don, Jones,, No Picture For user

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

  • #27295

    Matt Bloomfield

    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?

  • #27298

    Rob Simmers

    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, 
               @{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.

  • #27306

    Paul Goffar


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


You must be logged in to reply to this topic.