Comparing two external data sources and combining.

This topic contains 6 replies, has 3 voices, and was last updated by Profile photo of Paul Goffar Paul Goffar 1 year, 6 months ago.

  • Author
    Posts
  • #27279
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    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

  • #27280
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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.

  • #27287
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    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.

  • #27288
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    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.

  • #27295
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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.

  • #27306
    Profile photo of Paul Goffar
    Paul Goffar
    Participant

    Rob,

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

    Thanks!

You must be logged in to reply to this topic.