Print Friendly
test
Categories Tags 10 Comments Author Related Posts
  • No Related Posts

Featured Image

Attachments

Show Extras

It might not be common for every server administrator to need to import a large number of users at once. However, if you do, the process can be automated and save you massive amounts of time. It requires a little bit of leg work to set things up but once the process starts it’s amazingly fast. The basic workflow of this is to create a CSV file in Excel of all your users and their attributes. You would then import that file in Active Directory using a Powershell Script. The import itself takes seconds instead of spending 5-10 minutes per user the manual way.

List of Users

To get started we will need a list of users. If you are doing this as a lab experiment like me it could take you forever to create a list of users. Luckily the folks over at Fake Name Generator made this extremely easy, and surprisingly realistic. Their website allows you to create a CSV file of fake identities that you can then import into Active Directory. Head over to their site and customize your list of users. I used the following settings:

Screen Shot 2013-08-24 at 11.28.32 AM

Modify CSV File

Now that you have a CSV file with all your users and extra information, we need to format it so that certain fields can be imported. We will also need to add a few fields. For a list of all fields that Powershell can import check out Microsoft’s TechNet Article. In my file I used the following columns:

  • Username
  • GivenName (or first name)
  • MiddleInitial
  • Surname (last name)
  • Display Name (Full Name)
  • UserPrincipalName
  • StreetAddress
  • City
  • State
  • ZipCode
  • Password
  • TelephoneNumber
  • Occupation
  • Path
  • Office

Some of these items I want to go into more detail about because they are tricky to make.

  • Username, for my domain, will consist of First initial + Last name. So I created a new column in my CSV file and used this function to create the username: =CONCATENATE(LEFT(B2,1), D2) Where as B is the GivenName column and D is the Surname column. Example: Danny Eckes becomes DEckes.
  • UserPrincipalName, is the logon name and domain on the Account tab of an Active Directory user properties. Since this one column fills two items you need to make sure this is done correctly. The function I used for this column is =A2&”@eckescorp.local” Where as A is the Username column and the text @eckescorp.local is the name of my fake domain. Active directory will use the @ to separate the two items. If you misspell something here you can run into trouble. Example: DEckes becomes DEckes@eckescorp.local.
  • DisplayName, for my domain, is First name and Last name. You can also add Middle Initial if you wanted. However when looking at the Name field of Active Directory, First and Last name look fine for me. The function I used for DisplayName is: =B2&” “&D2 Where B is the GivenName column and D is the Surname column. Note that the quotes have a space in the middle of them. This separates the first and last name. Example: Danny Eckes becomes one cell of Danny Eckes.
  • Password, this column is the value I will use for the password. Since I am doing this for testing and I want all users to have the same password I just set it to Password123.
  • Path is the Active Directory Path to the Organizational Unit the user object will be created. It helps to read this from right to left. OU=Los Angeles,OU=ecorp – Users,DC=eckescorp,DC=local when read means to create the new user on the Eckescorp.local Domain Controller in side the Los Angeles Organizational Unit that is a child of the ecorp – Users Organizational Unit. You will need to adjust this to match your DC tree structure.

You can take a look at my completed CSV file here.

Create Powershell Script

Now that we have our CSV file we can create a script that will import specific AD values from the columns in the CSV file. Open up Notepad and save the file as a .ps1 file. Name it whatever is easiest for you. Your script will consist of 2 lines:

  • Line 1: Import the CSV file and for each row of the CSV do the next line. Make sure you have the CSV filename and path correct. In my case the CSV and the script are in the same folder.
  • Line 2: Match the AD fields to the columns of the CSV. -FIELDNAME is the AD object and $_.COLUMNNAME is the item from the CSV. Example -Name $_.DisplayName will place the value of the cell in the DisplayName column as the Name in Active Directory.
    • -AccountPassword is a tricky one to set. I would suggest copying mine. It pulls the cell from the Password column and converts it to a secure string which is required for passwords.
    • -Enabled $True sets the account as enabled.
    • -PasswordNeverExpires $True will set the option for password on this account to NOT expire.

For simplicity you can use the following script:

Run PowerShell Script

If you haven’t yet, you will need to make sure you have enabled Powershell scripts on your server. Follow my article here to do that. Put your CSV files and Powershell script on your DC, or server that you have Active Directory installed on. I put them in C:\Temp but if you’re going to run a lot of scripts you might want to create a C:\Scripts folder.

Launch Powershell as an Administrator. Then at the prompt change directory “CD” to get to the folder that holds your scripts. Once in that folder, type:

Screen Shot 2013-08-24 at 5.05.30 PM

The Script will run and in seconds import all your users. Head over to Active Directory Users and Computers to confirm all the users are in the right locations and have the right attributes.

Screen Shot 2013-08-24 at 3.14.52 PM

I ran into several snags while importing these users. I first ran the script with just 2 users in my CSV file until I got all the kinks ironed out. I then imported the larger CSV file. I hope this explanation helps you get a basic understanding of Powershell New-ADUser Bulk Importing. Feel free to leave comments below.

Series Navigation<< Create a Domain in Microsoft Server 2012Server 2012 Enable Remote Desktop (RDP) through Group Policy (GPO) >>

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*


10 thoughts on “Powershell Bulk User Import in Server 2012

  1. Reply Alex January 14, 2014 at 3:37 pm

    Thanks for the very easy step by step process. I was able to generate the CSV to the exact format and naming convention as yours. I named it user.csv. Powershell is enabled. When I run PS code I get the follow error:

    New-ADUser : cannot validate argument on parameter ‘Name,. The argument is null or empty. Provide and argument that is not null or empty, and then try command again.

    Original PS Code:

    Import-Csv .\user.csv | foreach-object {
    New-ADUser -Name $_.DisplayName -UserPrincipalName $_.UserPrincipalName -SamAccountName $_.Username -GivenName $_.GivenName -DisplayName $_.DisplayName -Initials $_.MiddleInitial -SurName $_.Surname -Description $_.Description -Department $_.Department -StreetAddress $_.StreetAddress -City $_.City -State $_.State -PostalCode $_.ZipCode -HomePhone $_.TelephoneNumber -Title $_.Occupation -Office $_.Office -Path $_.Path -AccountPassword (ConvertTo-SecureString $_.Password -AsPlainText -force) -Enabled $True -PasswordNeverExpires $True -PassThru }

    Any help would be greatly appreciated.

    • Daniel Eckes
      Daniel Eckes March 2, 2014 at 10:27 am

      Alex,

      Sorry to hear you’re having troubles. Since you’re getting a NULL error, I would check the spelling and case for you $_.DispalyName column in your csv file. It looks like it cannot find that column in your csv or that the value for it is empty. If everything is spelled correctly and the DisplayName field is empty, try putting a space in the cell in excel. Give that a whirl and see what happens.

    • SDV May 4, 2015 at 10:55 am

      Hi

      I had same issue with the Name error
      After some searching i found a post about specifying the delimiter
      changed my import part of script as follows and its working
      Import-Csv .\userImport.csv delimiter “;”

      Hope this helps
      Cheers

  2. Reply Neil Gascoigne May 12, 2014 at 11:45 am

    Very good and so elegant. Thanks! Not just for 2012 either, works with 2003 DCs with the AD PS support added 😉

    I’d start out with a plain text DisplayName. This is often how we are presented with a request for a new account, e.g. Joe Doe.
    The reset can therefore be derived.
    Therefore, the formula I’d use would be (cribbed from you and bits of the internet)

    GivenName (looks at DisplayName in column D and gets first word)-> =IFERROR(LEFT($D2,SEARCH(” “,$D2)-1),$D2)
    Surname (looks at DisplayName in column D and gets last word) -> =TRIM(RIGHT(SUBSTITUTE(D2,” “,REPT(” “,LEN(D2))),LEN(D2)))
    Username (takes GivenName(B) and Surname(C)) -> =LOWER(CONCATENATE(LEFT(B2,1), C2))
    UPN -> =A2&”@domain.local”

    I’ve used $ against GivenName to fix column parameters, making it easier to copy and paste

    Further improvements I guess would be to add columns for standard group memberships by team, therefore creating a basic matrix and amending the PS script accordingly. I could then have a customised PS script for each team. More research required there!

    • Christoph August 14, 2014 at 11:41 am

      Hi,
      have the same Problem like Alex had – first the error was in the Password field : null error, so I canceled the Password-command in the .ps1.
      Then I had the exact Problem like Alex: null error for Name(displayname)….

      any other idea?

  3. Reply Chet August 15, 2014 at 7:54 am

    Hi ,

    This looks really good and simple. However I am getting errors. It may well be that I have changed it a bit but I have kept the format of the CSV file as it was.

    The error I am getting is

    New-ADUser : The server is unwilling to process the request
    At C:\Temp\Import Users Powershell\ImportUsers.ps1:2 char:11
    + New-ADUser <<<< -Name $_.DisplayName -UserPrincipalName $_.UserPrincipalName -SamAccountName $_.Username -GivenName
    $_.GivenName -DisplayName $_.DisplayName -Initials $_.MiddleInitial -SurName $_.Surname -Description $_.Description -De
    partment $_.Department -StreetAddress $_.StreetAddress -City $_.City -State $_.State -PostalCode $_.ZipCode -HomePhone
    $_.TelephoneNumber -Title $_.Occupation -Office $_.Office -Path $_.Path -AccountPassword (ConvertTo-SecureString $_.Pas
    sword -AsPlainText -force) -Enabled $True -PasswordNeverExpires $True -PassThru}
    + CategoryInfo : NotSpecified: (CN=Hazel Barker…etcorp,DC=local:String) [New-ADUser], ADException
    + FullyQualifiedErrorId : The server is unwilling to process the request,Microsoft.ActiveDirectory.Management.Comm
    ands.NewADUser

    Maybe I'm missing something. I can send you the csv file if you need. I've only changed the contents not the headers

    Thanks

    Chet

  4. Reply Hassan December 11, 2014 at 1:28 pm

    Hi,

    I have issue… everything works fine except when I mention OU structure in the Path filed in the CSV file but when I add use manually with the -Path it works fine.
    Any idea why ? the error was Directory objects not found but I have created the OUs and it works when manually mention them.

    Now Imported after run the redirect user command.

    Thanks,

  5. Reply Matt April 29, 2015 at 3:01 pm

    looks good,
    one question, have you done any speed checks ? eg how long would it take to import and create 100k users, or 1m users etc ?
    thanks.

  6. Reply Sivaraam December 31, 2015 at 1:31 am

    Thank You very very much Danny…
    The script worked like a charm , I just had to check and make sure the Path attributes in excel were spot on and that the password satisfied Microsoft Password Requirements and BOOM !
    100 accounts in about 7 seconds on a Virtualbox VM !!

  7. Reply Rakesh March 1, 2016 at 4:49 am

    Hi Team,
    Can any body else help me to forward the sample .CSV file. I am trying to upload the bullk users its getting error to me. So please do the needful.

Copyright © 2013 DannyEckes.com. All rights reserved. | Site design by Daniel J. Eckes | Privacy