Need help determining why I am getting blank spaces in exported spreadsheet

Chris Newton 0
Viewed 44

How do I take the following code, and have it stop putting a blank line in between each result that isnt found in AD? I want to put a 'Not Found' entry for each line that isnt found, but not create blank lines anywhere.

This is what the resulting spreadsheet looks like:

Exported Spreadsheet

# Import the data from CSV file and assign it to variable
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")        | Out-Null
$ErrorActionPreference = 'Stop'
$notfound = 'Not Found'
$OpenFIleDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.Title = "Please Select a CSV File to process"
$OpenFileDialog.InitialDirectory = $InitialDirectory
$OpenFileDialog.Filter = "CSV (*.csv) | *.csv"

$OpenFileDialog.ShowDialog() | Out-Null
$Path = $OpenFileDialog.Filename

$user = Import-Csv -Path $Path

 Function Get-FileName($initialDirectory) {    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |
Out-Null

$SaveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
$SaveFileDialog.Title = "Where do you want to save the file?"
$SaveFileDialog.initialDirectory = $initialDirectory
$SaveFileDialog.filter = "CSV file (*.csv)|*.csv| All Files 
(*.*)|*.*";
$SaveFileDialog.ShowDialog() | Out-Null
$SaveFileDialog.filename
}
$SaveMyFile = Get-Filename

$user | ForEach-Object {
  $u = Get-ADUser -Filter "EmailAddress -eq '$($_.email)'" - 
properties mail, samaccountname, manager -ErrorAction Stop | Select 
mail, samaccountname, @{N='Manager';E={(Get-ADUser 
$_.Manager).Name}},@{N="ManagerEmail";E={(Get-ADUser -Property 
emailaddress $_.Manager).emailaddress}}
     [PSCustomObject]@{email = $u.mail; samaccountname = 
$u.samaccountname; Manager = $u.Manager; ManagerEmail = 
$u.ManagerEmail}
 if ([string]::IsNullOrWhiteSpace($u.mail)){
     [PSCustomObject]@{email = $notfound; samaccountname = $notfound; 
Manager = $notfound; ManagerEmail = $notfound}}} | Export-CSV -Path 
$SaveMyFile -NoTypeInformation
powershell csv
1 Answers

It's not entirely clear why your current code could be failing but this should work properly:

$params = @{
    Properties = 'mail', 'samaccountname', 'manager'
}

$object = {
    param(
        $mail,
        $samAccountName = 'Not Found',
        $manager        = 'Not Found',
        $managerEmail   = 'Not Found'
    )

    [pscustomobject]@{
        Mail           = $mail
        SamAccountName = $samAccountName
        Manager        = $manager
        ManagerEmail   = $managerEmail
    }
}

$user | ForEach-Object {
    # if there is no value in this column for this object
    if([string]::IsNullOrWhiteSpace($_.email)) {
        # skip it, go next
        return
    }
    $params['Filter'] = "mail -eq '$($_.email)'"
    $aduser = Get-ADUser @params

    if(-not $aduser) {
        return & $object -Mail $_.email
    }

    $manager = $aduser.Manager | Get-ADUser -Properties mail
    & $object $aduser.Mail $aduser.SamAccountName $manager.Name $manager.mail
} | Export-CSV -Path $SaveMyFile -NoTypeInformation
Santiago Squarzon0
Related