Export Office 365 Users to a CSV File

Organizations with large Office 365 deployments may need to export user information to a comma delimited file (CSV), which can then be used as the basis for creating reports. In this Ask the Admin, I’ll show you how to connect to Office 365 and export user information using PowerShell.

Setup PowerShell Management for Office 365

Before you can run PowerShell commands to manage or export information from Office 365, you need to make sure that you have the PowerShell cmdlets for Windows Azure Active Directory installed locally, along with the latest version of the Microsoft Online Services Sign-In Assistant. For more information on installing these components, see “How to Install the Windows PowerShell Cmdlets for Windows Azure AD and Office 365” on Petri IT Knowledgebase.

Export Office 365 User Information

First you need to authorize to Office 365 using an administrator account.

• Log in to Windows 8 and switch to the Start screen.
• Type powershell, make sure that Windows Azure Active Directory module for PowerShell is selected in the search results and press CTRL+SHIFT+ENTER to launch the console with administrative privileges. Give consent or enter administrative privileges if prompted.
• In the PowerShell console, type connect-msolservice and press Enter.
• In the pop-up Enter Credentials dialog, enter your Office 365 administrative username and password, and click OK.

Now that we are successfully authorized to Office 365, we can run the following command to get a list of licensed users:

Get-MsolUser | Where-Object { $_.isLicensed -eq “TRUE” }

Now let’s format the output and send it to a file:

Get-MsolUser | Where-Object { $_.isLicensed -eq “TRUE” } | Select-Object UserPrincipalName, DisplayName, Country, Department | Export-Csv c:\LicensedUsers.csv

Alternatively, if you don’t need a file to be generated and just want to work with the data quickly, you can use PowerShell’s OutGrid view.

Get-MsolUser | Where-Object { $_.isLicensed -eq “TRUE” } | Select-Object UserPrincipalName, DisplayName, Country, Department | Out-Gridview