Comparing CSV to Active Directory, then output fields to a table

Hey all,

I am trying to create a script that will take a CSV with about 5,000 users and have it compare against Active Directory, then if the employeeID’s match, to have an output made into a table for each user that matches. I think i’m pretty close but starting to get stuck in the logic as the foreach loop isn’t continuing but breaking, even with continue added to it. I at one point have the AD items being everyone as the variable, not as a per user variable.

Here is roughly what i have.

$Users = Import-CSV -Path C:\temp\Users.csv

foreach ($Item in $Users){

#Declares variables 
$FN = $User.FirstName
$LN = $User.LastName
$IDNumber = $User.Number
$HREmail = $User.BusinessEmail

$ID = Get-ADUser -filter * -Properties * | Select SamAccountName

#If the ID on the CSV matches ID in ActiveDirectory - Gather the user data (SamAccountName, mail, givenName, and SurName) and display it
     If ($IDNumber -match $ID){
        $Person = Get-ADUser -Filter * -Properties * -SearchBase "OU=Users,OU=,DC=Company,DC=com" | Select SamAccountName, mail, GivenName, Surname


Then i’d like for it to be populated into a table like the following

#Create Table with headers
$output = @()
$row = New-Object System.Object
$row | Add-Member -MemberType NoteProperty -Name "XRefCode" -Value $IDNumber
$row | Add-Member -MemberType NoteProperty -Name "EmployeeNumber" -Value $IDNumber
$row | Add-Member -MemberType NoteProperty -Name "FirstName" -Value $FN
$row | Add-Member -MemberType NoteProperty -Name "LastName" -Value $LN
$row | Add-Member -MemberType NoteProperty -Name "ElectronicAddress" -Value $ADUser.mail

welcome to the forums.

I’d recommend another approach. You may create a list of all AD user at once and compare this list against your CSV data with Compare-Object. I’d expect this to be way faster and less error prone than your approach with the loop. :wink:

1 Like

Exactly what Olaf said. With your current code you pull all ad objects with all properties once for each user. Should almost never need -Filter * -Property *


Taking a quick look and i totally was able to cut down the script to simply import csv and i did cut the properties down to the 4 i’m looking for. The compare-Object did show me what compared on the 2 headers i’m comparing. So far so good! Now I just need to export all the compared users into a table.

Export-Csv should do the trick. :wink:

Not fully. I need to take the items that were equal, which I have. Then for each of the equal items i need that to look into active directory and export each person in AD that it matches.

Why? You should already have them in the list you queried before. And … when you use the parameter -PassThru for your Compare-Object command you should have all you need already in the output.

Because the compares show me who exists in AD compared to the CSV, which is a great start. Then I need to have a foreach loop or something to search for each ID that was compared to AD and spit out specific properties. This is to update the HR side of things. they only have say userID, they want back firstname, lastname, email, and ID in a table.

I got this and I actually already answered this. If you use -PassThru for your Compare-Object command you will get the output including one of the input objects. If your imput object already has the properties you need you’re done.

Then if i have the data…its the table creation that’s getting me. I am using this…

Foreach ($Person in $Exist){
    #Create Table with headers
$row = new-object PSObject
$row | Add-Member -MemberType NoteProperty -Name "XRefCode" -Value $Users.SamAccountName
$row | Add-Member -MemberType NoteProperty -Name "EmployeeNumber" -Value $Users.SamAccountName
$row | Add-Member -MemberType NoteProperty -Name "FirstName" -Value $Users.GivenName
$row | Add-Member -MemberType NoteProperty -Name "LastName" -Value $Users.Surname
$row | Add-Member -MemberType NoteProperty -Name "BusinessEmail" -Value $Users.mail

#Adds to the array
$OutArray +=$row

When I Export-csv it comes out:
#TYPE System.Management.Automation.PSCustomObject

I assume some formatting needs to be done or i’m populating the table incorrectly.

When you use $Person as your loop variable you cannot use $Users for your object creation.

Try it this way:

$OutArray =
Foreach ($Person in $Exist) {
        XRefCode       = $Person.SamAccountName
        EmployeeNumber = $Person.SamAccountName
        FirstName      = $Person.GivenName
        LastName       = $Person.Surname
        BusinessEmail  = $Person.mail

I think i’m backwards here. Sorry for this and i do appreciate this.

I start importing the CSV (call it $Import).
Then I get the ADusers with properties needed (call it $ADUsers).
Then use compare-object between the 2 using employee IDs as the compared item and passthrough which is called ($Exist).

$ADUsers = All AD items
$Import = Imported CSV
$Exist = the file that compared the 2 (which now only shows the IDs)

When using a ForEach ($Person in $Exist) it’s only using the information inside the $Exist variable, right? So how do i get all the AD stuff from the $ADUsers variable into the table?

I’ll try to show what I mean …

$Import = @'
'@ | ConvertFrom-Csv | Select-Object -Property *,@{Name = 'ID';Expression = {$_.IDNumber}}

$ADUsers = @'
'@ | ConvertFrom-Csv

Compare-Object -ReferenceObject $ADUsers -DifferenceObject $Import -Property 'ID' -PassThru -ExcludeDifferent -IncludeEqual -OutVariable Exist

This compares the $ADUsers against the imported IDs. The result will be saved in the variable $Exist and outputted to the console like this:

ID Name  Mail            SideIndicator
-- ----  ----            -------------
1  Georg ==
3  Paul ==

You could pipe this - just as it is - to a Export-Csv.
If you insist on removing the column SideIndicator you could do

$Exist | Select-Object -ExcludeProperty SideIndicator

and pipe this to Export-Csv.

1 Like

I think I understand. It’s how i’m doing the compare-object. I think i’m stripping everything but the 2 fields to compare.

$Exist = Compare-Object ($Importfile).ID ($ADUsers).employeeid -IncludeEqual -ExcludeDifferent -Passthru

So the return only have the 1 column of data. It is comparing 2 columns named differently, so maybe i need to have it just fully compare and save to the variable, then do the foreach loop?

Or do exactly how Olaf showed without a loop??

1 Like

That would work, I just need to figure out how to compare 2 differently named columns. Then it could do without a loop too.

Your original requirements were to compare the ID and if it matches, output the user data. What has changed?

You could prepare the input objects before the comparison with a Select-Object with a calculated property to change a property name if needed.

I think he meant ID and IDNumber:wink:

Yes, the data is to match, but the column names are different.