locked
Powershell: Exporting Active Directory Info to CSV RRS feed

  • Question

  • Hello,

    I am working on a powershell script to get last name, first name, mobile number, and office number from active directory with name in the format "lastname, firstname".  I am able to grab the information and export it to a csv, which I am then able to open in excel.  What I am having trouble with is the name format.  My code is below:

    $users = get-aduser -filter * -Properties surname,givenname,mobilephone,officephone
    $csvpath = "filepath.csv"
    Remove-Item $csvpath -force
    
    $forloop = foreach($user in $users) 
    {
       ($user.surname + ", " + $user.givenname) + "," + $user.MobilePhone + "," + $user.OfficePhone
    }
    
    Add-Content $csvpath -value "Name,Mobile Phone,Office Phone"
    $forloop | Add-Content $csvpath
     

    When I run this and open in excel, the info is in four different columns instead of the three that I'd like.  I can change the comma in the foreach loop between the $user.surname and $user.givenname to a semicolon or most any other symbol, and get 3 columns with the name formatted like "lastname; firstname".  

    If anyone could help get the name in the correct format with a comma, that would be helpful!

    Thanks!

    Thursday, July 27, 2017 7:48 PM

Answers

  • Easy for PowerShell.

    Get-AdUser -filter * -Properties surname, givenname, mobilephone, officephone |
    	Select-Object @{ n = 'Name'; e = {"$($_.surname), $($_.givenname)"} }, mobilephone, officephone |
    	Export-Csv $csvfile -NoType
    


    \_(ツ)_/


    • Edited by jrv Thursday, July 27, 2017 7:54 PM
    • Marked as answer by kaysmith Thursday, July 27, 2017 8:01 PM
    Thursday, July 27, 2017 7:54 PM

All replies

  • Easy for PowerShell.

    Get-AdUser -filter * -Properties surname, givenname, mobilephone, officephone |
    	Select-Object @{ n = 'Name'; e = {"$($_.surname), $($_.givenname)"} }, mobilephone, officephone |
    	Export-Csv $csvfile -NoType
    


    \_(ツ)_/


    • Edited by jrv Thursday, July 27, 2017 7:54 PM
    • Marked as answer by kaysmith Thursday, July 27, 2017 8:01 PM
    Thursday, July 27, 2017 7:54 PM
  • That worked great. Thank you!
    Thursday, July 27, 2017 8:03 PM