[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 four different methods you can use to export users from Active Directory. Every single method results in creating a CSV file. If you can use PowerShell, we highly recommend the last method, as it is the quickest one.
Exporting users from Exchange 2003-2019
- First, you have to access Active Directory Users and Computers by going to Start > 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 (Microsoft 365), your CSV file will need more columns. In the Microsoft 365 admin center, 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 the *.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 the Text to Columns feature 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 in 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/2019 using the classic Exchange admin center
If you need to export users from Exchange 2013, Exchange 2016, or Exchange 2019, you can also use the classic 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 to a CSV file.
- Next, select the columns which you want to export to the 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 (Microsoft 365) import, follow the steps 7-9 from the previous method.
Exporting users from Exchange Online (Microsoft 365)
The UI-based feature to export users is also available in Microsoft 365 (Office 365). In fact, you can automatically prepare a CSV file in two ways. If you need to move users to another Microsoft 365 tenant, use the solution available in the Microsoft 365 admin center. If you want to recreate 1:1 mailbox configuration, taking retention policies, archive mailboxes, custom attributes and other Exchange Online specific features into account, go for the export option available in the Exchange admin center.
Microsoft 365 admin center
- Sign in to the Microsoft 365 admin center.
- Go to Users > Active users.
- Click Export users – all the users will be exported together with all the information necessary to recreate your user base in a new tenant.
- If you want to get the CSV file ready to import, use the Text to Columns feature in Excel, as explained here.
Exchange admin center
- Sign in to the Exchange admin center.
- Go to Recipients > Mailboxes.
- Click Export mailboxes and choose the option that fits your scenario:
- Export mailboxes in current list to export only those users (mailboxes) that are displayed on the list below. For example, you can first filter the mailboxes according to a predefined filter or create your own one (using the Filter option in the top right corner of the mailbox list) to narrow the scope of the users you want to export.
- Export all mailboxes to export all users.
Currently, the modern Exchange admin center (unlike the classic one) doesn’t let you choose which user’s properties you want to export, so the file will contain a complete list of users’ details.
- If you want to get the CSV file ready to import, use the Text to Columns feature in Excel, as explained here.
Exporting 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.
See also:
Does it work for Power BI Admin Portal too? If I wanna export active/licence users in .csv?
You can go to M365 Admin Center > Billing > Licenses, from there, you can export users to CSV. Just remember that Power BI is not only available as standalone license – it is included in some license packages (like Office 365 E5).
What is the option on PowerShell cmd to show the AD user is enabled or disabled?
You can use the following cmdlet:
Get-ADUser -Identity <user’s identity> -Properties Name,Enabled
if the Enabled property’s value is set to $true, the account is enabled.
How do you include the user’s manager in exporting to Excel? I don’t see any option.
Thanks.
Hi Glycel,
The export to CSV button is highlighted in the step 5. Is the option missing in your Users’ manager?
1st method is good, but what when users are in many OU?
Then you can either go OU by OU and consolidate your CSV afterwards or go with the PowerShell methods, which would be much quicker.
How do you import the .csv file back into Active directory – Users and Computers if you don’t have Office365?
Hi Dave,
Active Directory Users & Computers doesn’t have the option to import, so you are limited to PowerShell. The exact script will depend on which parameters you exported. In general, it should be something like that:
import-csv "your-csv" | % {New-ADUser -GivenName $_.GivenName ... -Enabled $True -AccountPassword (ConvertTo-SecureString $_.Password -AsPlainText -force) -PasswordNeverExpires $True}
I can’t believe I haven’t written a guide on how to import this data, yet. I’ll try to catch up with a detailed guide as soon as possible!
Easy to understand. Thank you for the article you shared about export users.
Good info
Thank You.
Looks like a few missing steps here.
1. The image in step 3 is the same as in step 4. I think you meant to show the column selection graphic.
2. The export only includes the items in the folder you have selected. How do you export users from all sub-OUs?
Hi Greig,
Thanks for noticing the misplaced screenshot! I don’t know how I missed this earlier.
If you want to export users from all sub-OUs, I recommend using PowerShell, as it can target all OUs at the same time. Using ADUC, you would need to go manually to each and every subfolder and repeat the exporting procedure.
Excellent Tutorial for AD export mechanism
Mamatha
Thank you. Will share with my IT Dept.
Wow, GREAT info, saved me a lot of work!
I will use this method to Import emails to Google accounts.
Thank you VERY much.
Jose Mejia
IS Analyst