How to Create AD Users from CSV

hello and welcome to another video tutorial presented by server Academy comm in this tutorial I'll be showing you how to write a PowerShell script that will create users based on a CSV or an Excel spreadsheet our script will also have an Open File dialog a way to predictably generate a password based on information we know like the first last name employee ID as well as automatically adding the users to specific groups that we specify in the spreadsheet now if you liked this video at all if you learn anything or if you appreciate the content that I'm putting out please click that like button hit the subscribe turn on the notification bell and leave me a comment telling me what you thought of the video that really helps me out and it makes it so I can afford to create more of these tutorials in the future now I have a new users CSV spreadsheet located on the C Drive of my computer if you'd like to download this same spreadsheet that I'll be using for this tutorial you can click the link in the description also on that page you're gonna find the source code for this script so if you have any trouble you can just copy and paste from that webpage so now I want to talk about how you can create your own spreadsheet if you'd like now I have this example spreadsheet that we'll be using for this tutorial but you can make a spreadsheet with any format any columns and any information you want the important thing is that when you save the spreadsheet it's got to be in a comma separated value or comma delimited format now if we right click on the file in Windows Explorer and we look at the raw data we can see that each column here is separated by a comma if you see something different like it's separated by tabs or tab delimited this is not going to work with the import CSV command in PowerShell so just keep that in mind you can make any spreadsheet and include any columns that you'd like just make sure that you save it as a CSV and it's comma delimited to get started writing this script I'm going to open PowerShell ISE I can do that by clicking start and searching for PowerShell ISE once it loads the first thing I'm going to do is save the script to the C Drive and I'll call it new users dot ps1 now I'm going to zoom in into this console so you can see what commands I'm typing let's start by importing the active directory module so I'll add a comment and then we'll write import - module space active directory and you'll notice I'm using tab completion so I just type in the partial command press tab and it'll auto complete the command for me this is good for a couple reasons one it saves me time and two it reduces the chance of me typing in a command wrong or spelling a name wrong next we'll load the windows forms component so I'm gonna make a comment just saying this is for the Open File dialog and it's going to be for load when is forms we'll type an opening brace followed by system dot reflection assembly and a closing brace next we'll type in two colons and we're gonna say load with partial name again I'm using tab completion here we'll type in open parenthesis and quote system dot Windows dot forms end quote and in parentheses now let's pipe that out - out - null just so we don't put any output into the console after we run this command if you're not familiar with the pipe command on my keyboard it's above enter and below backspace and I just hold shift and press that key and it enters what's called the pipe command what that does is it takes the output from the previous command and passes it to whatever we type after the pipe in this case we're saying out - null so that essentially silences the output of the previous command now it's time to create and show the Open File dialog box so I'm gonna go down and make a comment just saying that we're gonna create and show the dialog and we're gonna make a variable called dialog and we're gonna say this is equal to new - object system dot Windows forms dot Open File dialog now let's configure some settings for this dialog again we're going to type in the variable name dialog dot initial directory is equal to and I'm going to set it to the C Drive next let's configure the file extensions that can be selected we're going to type dialog dot filter equals and in quotation marks CSV and I'm going to do space open parentheses star dot CSV close parentheses and that means any CSV file do a pipe and start out CSV again and we'll end the quotation marks let's go down another line and let's say dialogue dot show dialogue as obviously is going to present the dialogue to the user and again we'll pipe this - out - null so we don't output any information to the console now if we execute the script will see the file dialog will appear and we can select new users dot CSV and click open now at this point in time in the script all it does is close the window and the script ends but if we switch over to the PowerShell console at the bottom of the ISE we can enter the dialogue variable and output all the information that we just captured with the Open File dialog here we can see the object file name contains the path of the file that we selected in the file dialog box so if we output dialogue dot file name we'll be able to access this path in our script now let's go down a couple lines and we're going to store the file path in a variable called CSV so I'll make this comment and on the next line I'm gonna say dollar sign CSV file is equal to dialogue dot file name so essentially whatever path we select in the dialogue box will be stored in CSV file next we're going to import the file contents into a variable after we ensure that the file path is valid now if the file path is not valid we're going to exit the script so if somebody opens the file dialogue box and clicks cancel for example we want to terminate the script we don't want it to continue on and try to make user accounts based on faulty data so we're going to accomplish this with an if statement so I'll just set up my if statement here and inside of a bracket instead of the if statement we're gonna say system dot IO dot file and we'll close that bracket and we'll put two colons and we're going to say exists followed by opening and close parentheses inside of those parentheses we're going to say CSV file the variable that we created earlier so essentially this is saying if the file exists then we're going to write host and we'll just say importing CSV on the next line we're going to say CSV is equal to import - CSV - literal path and that's going to be CSV file because this is a variable that contains the path to our CSV file and we'll wrap this inside of quotes now let's add an else statement let's say right - host and we'll say the file path specified was not valid and then we're gonna write exit because again if the file path isn't valid we want to quit the script okay so let's execute the script that we have so far and see what happens okay so here's the file dialog box I'm gonna select two new users and I'm gonna click open here right away we can see that it's importing the CSV now let's execute the script again and this time at the file dialog box let's click cancel and here we can see it says the file path was not valid so it's working perfectly at this point we're ready to iterate over each line in the CSV file so we're gonna make a comment just saying that we're gonna iterate over each line and next we're going to say for each in open parentheses user in CSV closed parentheses will do an opening and closing brace and we're gonna just say do cool stuff and will say right - host user dot email address and you can see that it's pre-populating all the columns in my spreadsheet now on the first run you won't see these auto-populated so you'll need to type it in manually or you'll need to run the import that CSV command to import it into CSV before that war okay so let's go ahead and execute this script in the console I'm going to select new users that CSV and click open and here we can see that it output the email addresses of each user in the CSV file so it's working perfectly okay so now we're back at the ISC and let's erase the do cool stuff in the right host line and now let's change the comment to say setting up variables and we're going to set up a variable for the username and we're going to say username is equal to user dot first-name and user dot last names we want to set up a username format a first name dot last name now we're gonna run into a couple issues but let's go ahead and wrap this in quotes and let's go down to the next line and let's just output the username all right now let's switch over to the PowerShell console and let's execute the script okay so we can see this is not the desired result that we want we basically output all the information of each user in each row now let's take a moment to understand why this is happening let's take an example where we just want to output today's date we could right right - host today is date but we'll notice that it just says today is date now we're actually trying to execute the date command if we just type in date and press Enter it outputs the full date what we actually need to do is wrap the words date in a sub expression operator that means we just need to do a dollar sign open parentheses whatever command we'd like to execute and close parentheses so now if we run this command will see that it says today is and then it outputs the current date and by executing the date command within the string so back at the ISE here we need to wrap the first and last name within a sub expression operator now let's run the script again and just make sure it's working as desired so we're gonna execute the script I'll select new users at CSV and we can see that it output the first and last name of every user now keep in mind we want to do first dot last name so we need to go back and add a period between the sub-expression operator and then let's reax acute the script and just make sure that it updates and here we can see that I have the first dot last name format so the last thing that we need to do to the user name is just make sure that we remove any spaces from the user name you might have certain scenarios where you have someone with the last name of jr. or the third added to their last name with a space character in between and we want to make sure that we remove that so we're going to do that by just typing user name equals user name don't replace in open parentheses and we're gonna do quote space quote comma and then for the next one we'll just do two quotes with no space in between and a closing parenthesis this means find the open space and remove it so now we're going to create a secure password for the user and we're gonna create this password based on a couple of things but first let's make the variables secure password and we're gonna make that equal to convert to - secure string now before we actually enter the value for this password let's talk about the format that we want to use instead of just using something like password one for all the new users we're gonna create a password that's based on their first and last name and their employee ID so we're gonna create a password that's going to be in the format of first name initial and we're going to add + last name plus employee ID plus some special characters so we'll just add some special characters to the end of the password that way if someone's trying to brute-force your password it'll be a little bit more secure now let's talk about how we're going to get the first initial if I pop over to the console and I sign a variable first name equals Paul when we output that variable we're gonna get the entire name now I can access the first initial by typing an open brace a zero and a closing brace I can also use for one two three you name it up to four which will return nothing because there is no fourth character so back at the IAC we're going to do an opening quote and inside of a sub expression operator we're gonna say user dot first-name and we'll do opening brace zero closing brace and we'll close the parentheses to close that sub expression operator now we're going to do another sub expression operator and this will be for user dot last name again we'll do another sub expression operator for user dot employee ID and then we're gonna do the special characters and I'm just gonna say exclamation mark at sign and pound sign or shift one two three on my keyboard be sure to add the closing quotation mark next we'll say - as plain text and - force to ensure that the password is created as a secure string so now it's time to create the active directory user so let's make a comment and just say create ad user and we're gonna run the new dash ad user command and I'm going to space twice because we'll be doing a multi-line command we're gonna say - name and we'll do an opening quote and - sub expression operators separated by a space and the first one's going to be user dot first name and the second sub expression will be user dot last name now at the end of this line we're gonna add a back tick which on my keyboard is above tab in to the left of the number 1 this allows us to word wrap our commands so this is gonna be a really long command and it's easier to read if we just wrap the command using backticks now let's go down to the next line and let's press tab until we get even with the - name we're gonna type - given name and this is going to be equal to user dot first-name now keep in mind you don't have to wrap this in quotes because we're not combining any variables together it's a better practice to wrap everything inside of codes but I'll leave that up to you now we're gonna hit space and do another back tick to signify a new line and we're gonna go down and say - user principal name and that's going to be equal to user name while at a back tick and go down to the next line and we're gonna say - Sam account name and this will also be equal to user name again another back tick we'll go down a line and we're gonna say - email address and that's going to be equal to user dot email address we'll hit the back tick again go down to the next line and we're gonna type in - description and this is going to be equal to inside of quotes and inside a sub expression operator user dot description and we'll add a back take and go to the next line next we're gonna add - office phone and that'll be equal to user dot office phone or what a back tick and go down the line we'll add - path and this will be equal to inside of quotes and a sub expression operator user dot organizational unit and we'll add a back tick and go down to the next line and we're gonna say - change password at logon and we're gonna set that to true now that requires users to change their password when they first log in of course we want to set this to true because we're assigning a temporary password to the user account let's add the back tick and I'm going to scroll the window down so the codes a little more centered and we're gonna say - account password and this will be the secure password that we created earlier and a back take and go down we'll say - enabled and this could be the column enabled so we could just say user dot enabled however PowerShell is going to regard that as a string and this parameter only accepts a value of the type boolean so no strings will be accepted if we ran the command with a string or like user dot enabled it would actually give us an error some of you might be wondering how I know what type of value that this parameter accepts and you can easily find this out by running the get - help command followed by whatever command you're trying to learn about now if we scroll up we see the - enabled and we can see that it accepts a boolean type the other way that you you would just execute the command and you would get an air and it would tell you that it only accepts type bullying so if I call user dot enabled we're gonna get the value of the column user dot enabled which is false now if we type in user dot enabled dot get type open and close parentheses is going to tell us the type of value that that has and here we can see the name of this type is actually a string so our command only accepts bullying's so this wouldn't work what we have to do is convert this to a type bullying now we can do that by inside of a sub expression operator typing an opening brace and system dot convert closing brace and to Cullens and we're going to say to bullying and inside of these parentheses we're going to say user dot enabled now if we execute this command we get false now let's run that command again by hitting the up arrow and let's add dot get type open and close parentheses at the end of the command we're gonna see the type is now a boolean which is exactly what we need so back at the ISE we need to do the same thing so we're going to wrap this user dot enabled in parentheses we're gonna make the outside parentheses a sub expression operator and we're gonna say in an opening closing brace system convert and this will be two boolean's and we can delete that parentheses since we already wrapped it and now we're actually providing a valid bulan to the parameter now let's go down a couple lines and let's make a new comment and just say that we're gonna write to the host that we created a new user and we'll use the bright - host command and we're gonna say in quotation marks created and then the username variable slash and inside a sub operator we're gonna say user dot email address in quotation marks you can include more information here if you'd like but this is all that I'm gonna include so now we need to make an if statement so let's start by writing the come of what we want to do so we're gonna say if this group column is not null in other words if we have groups listed in the group column then let's iterate over those groups if you're near specified and let's add the user to each of those groups now we can accomplish this by first checking to see if there's any data in the group's column so we'll write an if statement and we'll say if inside of the parentheses we'll say user dot add two groups CSV - and E for not equals and then just open quotes and closing quotes and we'll close the parentheses and do an opening brace and inside of this we're going to say user dot add two groups CSV and we're going to say dot split and inside of the split we're going to write a quote and say comma end quote and we'll in the parentheses and we're going to pipe that to a for each command and we'll do an opening bracket and closing bracket and then go inside the for loop so essentially what we're saying here is we're gonna take the add two groups column if it's not blank and we're gonna split those values based on a comma once we split them it's going to hand us an array and we're gonna iterate over each of these elements with a for each loop so the pipe means we're taking the output of the split command and we're passing that to our for each so we can iterate over each individual group now we're going to run the ad - ad group member and the identity of the group is going to be dollar sign underscore and this is the array or the current item in the array that we're iterating over and then we're gonna say - members and we're gonna say username and rap that inside of quotation marks now we're going to go down the line and we're gonna say right - host and in quotation marks added username - dollar sign underscore and we're gonna say group and close the quotes so that's just gonna output added whatever username we're currently on and then the group name that we added them to okay so now let's go to the end of the script and go down a couple lines and we're gonna say read - host - prompt and we're gonna say script complete dot dot press ENTER to exit and essentially this is going to pause the script once the script is done running and this is going to give the user a chance to just review the output and make sure everything went as planned once you press enter the script will exit so now all that's left to do is to test the script and see if it actually works so I'm gonna go to the C Drive I'm gonna right-click the script and say run with PowerShell here's the file dialog we'll click our example CSV and click open and we can see that it created the new users and it added the users to the groups so let's pop open active directory and let's just refresh the o you here and I can see here that the three user accounts for the admin oh you were created let's double click on paul dot he'll and make sure that the group memberships were indeed added to this user account here we can see that it has the domain admins enterprise sediments and schema admins membership now let's go down to the domain users oh you and here we can see the other user accounts that were listed in the CSV have been created also you'll notice that the description says user created by powershell script alright that's it for this tutorial I hope you enjoyed this one again if you did please leave a comment below if there's anything I can do better also leave a comment below I'm always looking to improve make sure that you like the video and subscribe and turn on the notification bell so you can get alerted when I make new video tutorials just like this one also if you're interested in learning more about PowerShell administration or Windows server administration be sure to check out server academy calm as we specialize in providing Windows Server IT training all right I'll see you guys in the next video

Channel: Server Academy

Views: 14,068

Keywords: Server Academy, Import CSV, PowerShell, SpreadSheet, New-ADUser, CSV, Automatically create AD users with PowerShell, Creating AD Users with PowerShell, Active Directory, Import users from CSV to Active Directory

Id: UxqN05aRKlg

Length: 22min 40sec (1360 seconds)

Published: Fri Jan 03 2020