Get-ADUser – output a blank line fo CSV file when no data is found

Issue

I’ve cobbled together (from various online sources) a script that can return data about AD users from a CSV file. It can clearly mark when a user has NOT been found, but perhaps just as important to me is a way of outputting a blank line to the CSV file when the input CSV file also has a blank. That would save a lot mucking around in Excel to make sure that all the blank lines correspond to each other (and subsequently where user data has been found). A 1-to-1 comparsion is the goal.

Here is what I have…

$ImportCSV = "C:\Users\x\Desktop\testCSV_in.csv"


$Names = Import-Csv -Path $ImportCSV

$Results = foreach ($Name in $Names) {
    $filter = $Name.samAccountName
    
    $User = Get-ADUser -Filter "SamAccountName -like '$filter'" -Properties Samaccountname, Givenname, Surname, EmailAddress, Name

    #blank lines input CSV
    if ( $User.SamAccountName -eq ""  ) {
        # please help
    }

    # found user
    if ( $User ) {
        $User |
        Select-Object -Property Samaccountname, Givenname, Surname, EmailAddress, Name            
    }

    # not found user
    else {
        [pscustomobject]@{
            SamAccountName = 'MISSING ACCOUNT'

        }
    }
}

$Results | Export-Csv -Path "C:\Users\x\Desktop\testCSV_out.csv" -NoTypeInformation -Encoding UTF8

All the possible combinations that I can think of for $User.SamAccountName -eq "" just return back a CSV file that doesn’t reflect the gaps that I purposely introduced (to mimic real use-cases).

I am sure, it’s a just a line or two code that’s needed. Thanks.

Solution

If you want a normalized export you need a normalized Object, meaning, all columns for your CSV must exist for all lines, even if they’re $null. Try this code, see if it works:

$out = {
    param($samAccountName)

    [pscustomobject]@{
        Samaccountname = $samAccountName
        Givenname      = $User.GivenName
        Surname        = $User.SurName
        EmailAddress   = $User.EmailAddress
        Name           = $User.Name
    }
}

Import-Csv -Path 'C:\Users\x\Desktop\testCSV_in.csv' | ForEach-Object {
    if([string]::IsNullOrWhiteSpace($_.samAccountName)) {
        return & $out -samAccountName 'User Not Found on CSV'
    }
    $params = @{
        Properties = 'Samaccountname', 'Givenname', 'Surname', 'EmailAddress', 'Name'
        LDAPFilter = "(SamAccountName={0})" -f $_.samAccountName
    }
    $user = Get-ADUser @params

    if(-not $user) {
        return & $out -samAccountName 'User Not Found on AD'
    }

    & $out -samAccountName $user.samAccountName
} | Export-Csv -Path "C:\Users\x\Desktop\testCSV_out.csv" -NoTypeInformation -Encoding UTF8

Edit: Import-Csv, same as ConvertFrom-Csv will skip empty lines. At least one column has to be populated.

Answered By – Santiago Squarzon

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0