Importing users to Active Directory with Excel and PowerShell

hey class I'm going to show you how I did the import users function with Active Directory and the Excel spreadsheet that we were given in the class resources our first thing I'm gonna show you is my import folder that's on my desktop of my host machine this isn't the virtual machine yet I have that original excel file with the configurations that I made to the different cells so here we have the people's full name correct yeah the full name there might be spaces in between first middle and last I trimmed out any extra spaces and column B you still have the full name column C is the first name which takes what it sees over here as the first word and from the first space to the left returns that word so first name the middle name crazy calculation but it basically looks at the spaces between the first middle and last names and where it finds the two spaces it takes the middle word out and returns it as the middle name and then the last name and column E takes all the characters from the right side of the word the right side of the cell and moves left until it finds the first space and keeps only those characters to the right of that in column F I concatenated the first name added a period and then the last name to make the usernames I realized that even though I did this there are duplicate names throughout this here document so you're gonna see that pop up as an error when we actually run it in PowerShell column G is all the random passwords I had set up for each person every time I do something different on this excel document those passwords were chained will change and then column H is dependent on column I call them I lists all my organizational units for my company and I have four sites for my company so I've got four of them here and in this column H it will randomly select those OU's and apply it for that particular user so that's what I did for the Excel spreadsheet an order for PowerShell to be able to read this stuff I have to save this as a CSV so I'm gonna go to file save as saving it on my important folder that I'm in I'm gonna click on save as type and come on down to csv comma-delimited it's gonna give me an error saying I'm gonna lose some features that's okay doesn't matter all right I'll save that and close it and so now in my original import folder I have three files the user's excel spreadsheet I just messed with the users CSV that I just exported and my PowerShell file before I open the PowerShell file let me just show you what the CSV looks like I'm gonna open it with notepad and it just looks like a bunch of garbage I know in notepad but it's using all those cells where those cells go from one together it uses commas as delimiters and this is how powershell is gonna read this alright that powershell file if you have Windows 10 you should have PowerShell on your computer I would assume so you should be able to open this with Windows PowerShell and you can or PowerShell ISE specifically and you should be able to see what's in this PowerShell file from the top you'll see that what I'm going to do is I'm going to import that users dot CSV file to do this and we are going to move all of this to the server which is the guest machine in VMware right so that's where we're gonna draw everything from these are all the variables we're gonna use a username password first-name lastname organizational unit I didn't come up with emails for everybody yet and what happens is we're going to take that full list and we're going to try to apply all these attributes to them within Active Directory if a user already exists you'll see this warning a user account has already been exit or already created gonna happen like I said there's a whole bunch of duplicate names in that sheet but this is just showing you how we get a lot of men at one time all right I'm not gonna do anything here because I'm still on my host machine I'll go and close that up what I'm gonna do is I'm gonna copy it real quick and open up my server in VMware okay so now that I'm in my server if you don't like I should be able to copy paste this in here okay so I pasted that folder from my host computer to my guest computer if you don't have that option I do believe you need to go to preferences and workspace check enable all shared folders by default and that should allow you to copy from your host into your virtual machine and from virtual machine to virtual machine if you so choose to do that okay so here I'll open up my import folder it will open there we go slide that over to the left right I don't have excel on my guest machine which is why it doesn't show the Excel icon on this guy but doesn't matter we're not gonna use that remember we're gonna use the the users CSV file and again I can open that notepad on my guest machine to look at it and for testing purposes what I'm gonna do is I'm gonna remove a lot of these names just so you can see a few of them go on then we'll load everybody else so what I'll do is I'll copy and paste this and we're its users I'll edit that again and starting from here I'll go all the way down delete all these users and we'll load just these people for the time being I'll save that okay now here's a kind of important part this this got me for a while I'm gonna open up PowerShell ISE on my server machine the virtual one but I need to open it as an administrator I ran this so many times and I kept getting access denied when I ran this and I thought I had done something horribly wrong but it turns out I wasn't running this as an administrator so make sure you do that I don't really close that all right so when PowerShell ISE opens up it'll open up something like this I'm going to open up that users ps1 file all right so from desktop import my Windows shell PowerShell file again it shows me the contents of what I want to do here this console window at the bottom in blue is gonna return the output or show you anything that's happening as it runs okay remember I took off a whole bunch of names from the users list I am importing from see users administrator site one desktop import users CSV it's important that when you make your file path wherever you save this file if you chose to use it that there are no spaces anywhere in this file path I ran into issues there couldn't figure out what was going on and I was getting errors down in the console area this used to say import space users and then the slash then users CSV and it kept giving me errors so I just made an import import and now it works just fine I hope all right so once I've got that in there everything's ready to go I'm gonna hit play and we'll see if we get any errors all right the attempt was made to add an object with the name that it's already in use okay did I already have people in there maybe I did all right let's go to the actual tools on my dashboard and select Active Directory users and computers oh I actually have lots of people there from when I tested this before so I guess what I'll do is delete everyone and then try to re Adam it's also important when you put euro use from the Excel spreadsheet into your Active Directory that you put them directly under your main server ok in this case mine's wind namaste tastes calm I had originally placed it underneath these site one two three four underneath namaste taste here and this wouldn't work so I had to I have to figure that out how I could nest them within another organizational unit using PowerShell but for the time being I have them nested directly underneath the main domain all right so let me delete all these people as well all right so now I have no users in Active Directory let's go back to my Power Cell I will try to run this again and we'll see what happens okay all white text is usually all good all right and according to this I should have put in one two three four five no hole but a dozen names so let's go back into the server users and computers and there we go site one has one two three four people site two as for site three as one and inside floor also has four so now that we know that this is working what I can do is go back to my list of users and delete that CSV file and rename the copy that has all the people into just users and then I can learn my PowerShell command again now we already have those users in there so I expect to see orange text that tells me that a user account with blah blah blah already exists and again because there's a whole bunch of duplicates in that excel file I'm gonna see a lot of those about 300 if I counted right let's hit this there they go all right I don't know what this file it error is right here though - that are in red that's interesting those ones but for right now everything is going through and this can take a while so I guess one way I could have alleviated the user already exists in Active Directory would be to also incorporate the middle initial for a lot of these users names but if you've looked through that excel spreadsheet a lot of these users don't have a middle initial we could easily create some sort of formula in Excel that would automatically fill in the U the middle name with random text or even people's first names from the cell below them as their middle names just stuff like that but this was just really a test I think and can we import a massive amount of users and there we go once we get the the white text at the bottom that is the end of it and if I go back into my server tools Active Directory users and computers I can check all my sites site one yep site to site three site four and all my users have names passwords and they're just there so power sells real powerful and we could have added a whole bunch of more options if you actually look at the PowerShell window itself on the right side it lists all the different actions I guess you could say in PowerShell that you could have added so if I wanted to assign a printer to a user they could have been assigned a printer and it's cool they tell you the actual details of everything - all right exactly how you would or I'm sorry you didn't put the details in it exactly how you'd want to configure and then once you fill that out you could hit insert and it would insert it into the window down here but that's it that's how I inserted a whole bunch of users into Active Directory with Excel and Power Cell thanks

Channel: Jorel

Views: 8,775

Keywords: excel, powershell, GCU, ITT-121, Active Directory

Id: 6EsMAy6g1xU

Length: 13min 30sec (810 seconds)

Published: Wed May 01 2019