[Update]: This blog post was updated on March 19. 2019
You might need to export users from Active Directory in more than one situation. Good examples include Exchange migration and creating a test Exchange environment. You can imagine how painful it would be to do those tasks manually, especially in a large organization. Luckily, users can be exported easily from Active Directory and saved into a CSV (comma separated value) file. In this article I am going to show you how to do it. Watch the video below for a quick walkthrough and read the following article if you want to learn more.
Before we start, a quick word on the CSV files which are used to save users’ data. Lists created in this file format have headers in the first row. Those headers correspond to the names of AD users’ attributes. Below the headers, a list of users begins, each row for one user. Users’ attributes are sequenced exactly as in the headers. It is important to know that not all attributes have to be filled. However, if you want to leave certain fields blank, you should stick to the order from the first row. You can edit CSV files using e.g. notepad or Excel. Keep in mind that Excel is much better in this case, as it allows filling many fields at once with the same value. Besides, tables are much better at organizing data, which will come in handy.
Below, you have three different methods you can use to export users from Active Directory. Every single method results in creating the same CSV file. If you can use PowerShell, we highly recommend the last method, as it is the quickest one.
Exporting users from Exchange 2003-2016
- First, you have to access Active Directory Users and Computers by going to Start menu > Administrative tools > Active Directory Users and Computers:
- An AD administrative tool will appear. Choose the name of your domain and go to “Users”
- A complete list of users will appear. However, there are also Security and distribution groups included. To get rid of them, use filters. Instead of showing all types of objects, choose “show only the following types of objects”, check “users” and click the OK button.
- The list will be shorter now. The next step is choosing which attributes to export to a csv file. Now, which fields you choose depends on what you need your CSV file for. If you want to create a migration batch, your file will need only 3 columns: EmailAddress, Password, and ForceChangePassword.
If you want to use the list to create users in Office 365, your CSV file will need more columns. In Office 365 Admin’s Centre, Microsoft provides a template with all the required headers. Click here to download it.
Now, go to view > Add/Remove Columns…
- In here, you can either choose name and Email Address for creating a migration batch, or the attributes you can see in the picture above if you want to bulk add users in Office 365. As you can probably see, not all attributes from the previously mentioned template file can be listed. That is why you will need to edit the CSV file manually. But before that, click OK to apply the changes.
- By clicking on “Export list” in the top menu, you can finally export your users. You just have to name the new file and make sure that it is saved in *.csv format.
- If you open the file in Excel, it will probably be delimited with commas, but just in one column. To make editing easier, use a “Text to Columns” function and follow the wizard.
- Now, the last step is to copy the data you have recently exported into the spreadsheet from the step 4 of this guide. Remember that you do not have to fill the empty fields. Leaving them blank will tell Office 365 that those values are not specified. The fields that cannot be blank are User Name and Display Name
- Process of creating the csv file for a migration batch is almost the same. Instead of 15 headers, you have only 3: EmailAddress, Password and ForceChangePassword. Only Email field must not be left blank and in case a single sign-on solution has been implemented, ForceChangePassword should have the value of “false”.
Exporting users from Exchange 2013/2016 or Office 365 using Exchange Admin Center
If you need to export users from Exchange 2013, Exchange 2016 or Office 365, you can also use Exchange Admin Center. The steps you need to take are as follows:
- Access your Exchange Admin Center, go to recipients tab, click more options and choose “Export data do CSV file”.
- Next, select the columns which you want to export to CSV file and click “export”:
- Again, like it was with the previous method, there are properties you will have to add manually. If you want to prepare the resulting CSV file for Office 365 import, follow the steps 7-9 from the previous method.
Export users from Active Directory using PowerShell
There is another, much quicker way to accomplish the title task. You can export users from Active Directory using PowerShell. The cmdlet below exports a complete list of my company’s users to a csv file.
Get-ADUser -Filter 'Company -like "Alpha*"' -Properties * | Select -Property EmailAddress,GivenName,Surname,DisplayName,Title,Department,Office,OfficePhone,MobilePhone,Fax,StreetAddress,City,State,PostalCode,Country | Export-CSV "C:\\ADusers.csv" -NoTypeInformation -Encoding UTF8
Now for a quick word of explanation. Get-ADUser cmdlet can either pull only one user from Active Directory, using –Identity parameter, or it can pull many users at once with –Filter or -LDAPFilter parameters. In this example, I use the filter to export all users who have the Company AD field starting from “Alpha”. Thanks to that, I will get only the users I want, without e.g. Healthmailboxes which would appear if I used “-Filter *”, instead.
The cmdlet creates ADusers.csv file on the C drive. If you want to use this file to import users into Office 365, you need to take 2 easy steps. First, you have to substitute the first row with the right headings from the file mentioned in step 4 above. Then, go to edit > replace and remove all quotation marks:
As you can see, PowerShell gets the job done. The cmdlet exports all AD properties used in Office 365 import file, so there is no need to spend any time copying and pasting columns like it was the case in previously described methods.
All 3 methods result in the creation of a CSV file. Which method you use depends on your environment and preferences.