Powershell to CSV compare
<p>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.</p>

<p>This is what I'm working with:</p>

<pre><code>$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 "(&amp;(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} 
} 
</code></pre>

<p>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:</p>

<pre><code>$Results = foreach ($line in $csv) 
</code></pre>

<p>and then use </p>

<pre><code>$Results | export-CSV $outpath -NotypeInformation 
</code></pre>

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

<p>Any assistance would be appreciated.</p>

<p><strong>UPDATE - Final code</strong>
This is the final code I went with.  This compares the users in the csv with AD users in the parent and child domain. It uses the email address field to match the users. It grabs the Canonical Name so I can see the OU the user is in and if the user is not found, it reports that in the Canonical Name field.</p>

<pre><code>$path = "$env:userprofile\Desktop\InFile.csv"
$outpath = "$env:userprofile\Desktop\OutFile.csv"

# Importing CSV file 
Import-Module ActiveDirectory
$Users = Import-Csv $path |


ForEach-Object {
     Write-Progress -Activity "Comparing Imported File to Active Directory" -Status "Processing $($_.Email)" 

# Comparing CSV file to Domain.com users
If (
    $Value1 = (Get-ADUser -LDAPFilter "(&amp;(objectclass=user)(mail=$($_.Email)))" -server Domain.com -Properties CanonicalName).CanonicalName)
     {$_ | Add-Member -MemberType NoteProperty -Name CanonicalName -Value $value1 -PassThru}

# Comparing CSV file to child.Domain.com users
ElseIF (     
    $Value2 = (Get-ADUser -LDAPFilter "(&amp;(objectclass=user)(mail=$($_.Email)))" -server child.Domain.com-Properties CanonicalName).CanonicalName)
    {$_ | Add-Member -MemberType NoteProperty -Name CanonicalName -Value $value2 -PassThru}

# Writing output for users not found in either domain
Else {$_ | Add-Member -MemberType NoteProperty -Name CanonicalName -Value "Email Address not found in Active Directory" -PassThru}

#Exporting to CSV file


        New-Object -TypeName pscustomobject -Property @{
        Email = $_.Email
        CanonicalName = $_.CanonicalName
        LastName = $_."Last Name"
        FirstName = $_."First Name"

    }

} | Select-Object LastName, Firstname, Email, CanonicalName | Sort-Object CanonicalName | Export-CSV $outpath -NoTypeInformation
</code></pre>