Author Posts

June 11, 2018 at 9:24 am

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'

June 11, 2018 at 9:43 am

$result = $outarray |Select-Object 'first name', 'last name', 'e-mail address', 'business phone', 'office'

found it 🙂 select-object

June 11, 2018 at 9:20 pm

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

June 13, 2018 at 12:30 pm

Thanks Rob, much appreciated