PSCustom Object column ordering & Export-csv

Welcome Forums General PowerShell Q&A PSCustom Object column ordering & Export-csv

This topic contains 3 replies, has 2 voices, and was last updated by

 
Participant
6 months ago.

  • Author
    Posts
  • #102229

    Participant
    Points: 30
    Rank: Member

    Hi All bit of an odd one, i can get the data to look exactly how i want it in the Array, however exporting to CSV gives something wiered

    
    Import-Module ActiveDirectory
    
    $users = Get-ADUser -Filter * -SearchBase "ou=newcastle, OU=United Kingdom,OU=Europe,DC=global,DC=contoso,DC=com" -Properties mail, title,telephonenumber, physicalDeliveryOfficeName, employeeid | ? {$_.employeeid -like "0*"} | select mail, title,telephonenumber, physicalDeliveryOfficeName, givenname,surname
    
    $outarray = @()
    
    foreach ($user in $users)
    {
        
    
    
    
    $properties = @{'First Name' = $User.givenname
                    'Last Name' = $User.Surname
                    'E-Mail Address' = $user.mail
                    'Job Title' = $user.title
                    'Business Phone' = $user.telephonenumber
                    'Office' = $user.physicalDeliveryOfficeName
                    }
    
    $Obj = New-Object -TypeName psobject -Property $properties
    Write-Output $obj 
    
    $outarray += $obj 
    
    }
    
    $result = $outarray | ft 'first name', 'last name', 'e-mail address', 'business phone', 'office'
    
    $result | Export-Csv c:\temp\Proxyclick.csv
    
    

    typing $Result displays correctly, colums sorted and formatted

    
    First Name Last Name         E-Mail Address                      Business Phone          Office                         
    ---------- ---------         --------------                      --------------          ------                         
    Lloyd      Pickering         Lloyd.Pickering@contoso.com            +44 191 238 xxxx        Newcastle Office               
    Gillian    Wright            Gillian.Wright@contoso.com             +44 191 238 xxxx        Newcastle Office               
    Daren      Carr              Daren.Carr@contoso.com                 +44 191 238 xxxx        Newcastle Office               
    Andrew     Hales-Hill        Andrew.Hales-Hill@contoso.com          +44 191 479 xxxx        Newcastle Office             
    
    

    however the csv looks like

    
    #TYPE Microsoft.PowerShell.Commands.Internal.Format.FormatStartData					
    ClassId2e4f51ef21dd47e99d3c952918aff9cd	pageHeaderEntry	pageFooterEntry	autosizeInfo	shapeInfo	groupingEntry
    033ecb2bc07a4d43b5ef94ed5a35d280				Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo	
    9e210fe47d09416682b841769c78b8a3					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    27c87ef9bbda4f709f6b4002fa4af63c					
    

    without the sorting section, it exports fine

     
    $result = $outarray | ft 'first name', 'last name', 'e-mail address', 'business phone', 'office'
    
  • #102230

    Participant
    Points: 30
    Rank: Member
    $result = $outarray |Select-Object 'first name', 'last name', 'e-mail address', 'business phone', 'office'

    found it 🙂 select-object

  • #102293

    Participant
    Points: 305
    Helping Hand
    Rank: Contributor

    Hey Mark. Glad you figured your issue out. Wanted to show you a better way to do what you're attempting. The biggest is filtering the employee ID after Get-ADUser, this is basically returning ALL users and then filtering versus only returning the users that you want. Rather than doing -Filter *, you should specify your filter there.

    Next, there is no reason to loop through all the users to create a custom object. You can accomplish this with calculated expressions. Take a look at the code below:

    Import-Module ActiveDirectory
    
    
    $params = @{
        Filter = {EmployeeID -like "0*"}
        SearchBase = "ou=newcastle, OU=United Kingdom,OU=Europe,DC=global,DC=contoso,DC=com" 
        Properties = "mail","title","telephonenumber","physicalDeliveryOfficeName","employeeid" 
    }
    
    $users = Get-ADUser @params |
             Select-Object -Property @{Name='First Name';Expression={$_.givenname}},
                                     @{Name='Last Name';Expression={$_.surname}},
                                     @{Name='E-Mail Address';Expression={$_.mail}},
                                     @{Name='Job Title';Expression={$_.title}},
                                     @{Name='Business Phone';Expression={$_.telephonenumber}},
                                     @{Name='Office';Expression={$_.physicalDeliveryOfficeName}}
    
    $users | Export-Csv c:\temp\Proxyclick.csv -NoTypeInformation
    
  • #102403

    Participant
    Points: 30
    Rank: Member

    Thanks Rob, much appreciated

The topic ‘PSCustom Object column ordering & Export-csv’ is closed to new replies.