Comparing two external data sources and combining.

Welcome Forums General PowerShell Q&A Comparing two external data sources and combining.

This topic contains 6 replies, has 3 voices, and was last updated by

 
Participant
3 years, 9 months ago.

  • Author
    Posts
  • #27279

    Participant
    Points: 1
    Rank: Member

    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

    Participant
    Points: 41
    Rank: Member

    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

    Participant
    Points: 1
    Rank: Member

    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

    Participant
    Points: 1
    Rank: Member

    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

    Participant
    Points: 41
    Rank: Member

    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

    Participant
    Points: 639
    Helping Hand
    Rank: Major Contributor

    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

    Participant
    Points: 1
    Rank: Member

    Rob,

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

    Thanks!

The topic ‘Comparing two external data sources and combining.’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort