How to: create users in Office 365 from CSV file with PowerShell
I recently had a project where the requirement was to create approx. 400 users across 8 different Office 365 tenants and to assign appropriate licenses to users. It is possible to create those accounts using Office 365 tenant admin pages - import CSV file of supported structure with user accounts using existing wizard. There is also another option - to use PowerShell. Using PowerShell has several advantages to the wizard, as the CSV file doesn't have to be with pre-defined format, column names and ordering of the columns in CSV could be customized to better fit specific user procedures and policies.
As a basis for my script, I used one of the scripts used in Office Garage video "Garage Series for Office 365: PowerShell basics and the space race" which I slightly modified to support custom CSV format (with column names localized to Bosnia and Herzegovina and ordered appropriately). It is important to mention that, in order to support local names and surnames with characters such as ČĆŽŠĐ CSV has to be saved in UTF-8 format.
Similar to my previous blog post, for creation of user accounts in Office 365 I am using New-MsolUser cmdlet which is a part of Azure Active Directory Module for Windows PowerShell. For New-MsolUser cmdlet, it is required that -UserPrincipalName and -DisplayName parameters are populated. All other parameters are optional. As part of user creation process, I can assign license to an user, so it is crucial to retrieve licensing SKU for the tenant, using Get-MsolAccountSku cmdlet. As I had to share temporary passwords with my new users (which is a default option of the New-MsolUser cmdlet), I had to save the script output as a file. For that purpose, I used start-transcript/stop-transcript cmdlets. The script output has to show full output, so I also used Format-Table option. Once I imported CSV file, I can use columns as arguments for New-MsolUser cmdlet. Each row of the CSV file is one object, and I can access value of each column using $_.ColumnName syntax.
Here is full sequence of PowerShell commands:
Start-Transcript
Connect-MsolService
Get-MsolAccountSku
Import-Csv -path "path\to\my\csvfile.csv" | ForEach-Object { New-MsolUser -UserPrincipalName $_.UserEmailOnTenant
-FirstName $_.Name -LastName $_.LastName -DisplayName $_.FullName -Title $_.UserTitle
-Department $_.Dept -Office $_.OfficeNr -PhoneNumber $_.Tel -StreetAddress $_.Address -City $_.City
-State $_.Region -PostalCode $_.ZipCode -Country $_.UserCountry -UsageLocation "BA"
-LicenseAssignment "LicenceNameRetrievedInPreviousCommand"| Format-Table -AutoSize}
Stop-Transcript
More info and downloads:
