Home Ask Login Register

Developers Planet

Your answer is one click away!

YEMyslf February 2016

Powershell to CSV compare

Hello I'm trying to use some code I found on here to compare a CSV with Active Directory. I have a csv file with a list of users. I want to check this file and see if anything in the "Email" column from the spreadsheet matches an email address from AD. If it does, I want to list that email address and include the Canonical Name from AD so I can easily see what OU the user account is in.

This is what I'm working with:

$path = "C:\Scripts\Import.csv"
$outpath = "C:\Scripts\Export.csv"
$csv = Import-Csv $path 
Import-Module ActiveDirectory

foreach ($line in $csv) 
    $User = Get-ADUser -LDAPFilter "(&(objectclass=user)(mail=$($line.Email)))"  -Properties CanonicalName
    If ($User -ne $Null) {"User does exist in OU" + $line.Email + $User.CanonicalName}
    Else {"User not found in AD OU  - " + $line.Email} 

I've been able to modify this to suit my needs but I'm having some trouble piping the results out to a CSV file. Running the script as it's shown above outputs what I want to the screen but I'd like to have it in a CSV format. If I do something like:

$Results = foreach ($line in $csv) 

and then use

$Results | export-CSV $outpath -NotypeInformation 

I get the csv created but it just includes a the string value, header for Length and then a numeric value for each line. I can use Out-File to send the results to a txt file, which includes the same results that were displayed on the screen, but I'd really like this to be a csv, not txt file. I believe I need to reference the properties of the csv file and AD in order to build these into my export file but I'm having trouble doing that as I'm not sure how to build in the status of whether the user was found or not.

Any assistance would be appreciated.

UPDATE - Final code This is the final code I went with. This compares the users in the csv with AD users in the p


beatcracker February 2016

I'm not sure what resulting CSV should look like, so this code just adds CannonicalName using Calculated Properties to Import.Csv and saves it as Export.Csv.

$path = "C:\Scripts\Import.csv"
$outpath = "C:\Scripts\Export.csv"
Import-Module ActiveDirectory

Import-Csv -Path $path |
    Select-Object -Property *, @{
        n = 'CanonicalName'
        e = {(Get-ADUser -LDAPFilter "(&(objectclass=user)(mail=$($_.Email)))" -Properties CanonicalName).CanonicalName}
    } | Export-Csv -Path $outpath -NoTypeInformation


This version will create a new CSV file with 3 columns: UserExistInOu, Email and CanonicalName if any:

Import-Csv -Path $path | ForEach-Object {
    $UserExistInOu = $false
    if($CanonicalName = (Get-ADUser -LDAPFilter "(&(objectclass=user)(mail=$($_.Email)))" -Properties CanonicalName).CanonicalName)
        $UserExistInOu = $true

    New-Object -TypeName pscustomobject -Property @{
        UserExistInOu = $UserExistInOu
        Email = $_.Email
        CanonicalName = $CanonicalName
} | Export-Csv -Path $outpath -NoTypeInformation

Post Status

Asked in February 2016
Viewed 1,877 times
Voted 9
Answered 1 times


Leave an answer

Quote of the day: live life