Use PowerShell to Read a CSV file and Create Active Directory User Accounts

Use PowerShell to Read a CSV file and Create Active Directory User Accounts

  • Comments 5
  • Likes

Summary: Learn how to use Windows PowerShell to read a CSV file and to create new user accounts in Active Directory.

Microsoft Scripting Guy, Ed Wilson, is here. Once again we return to Windows PowerShell Blueville with guest blogger Sean Kearney.

Mr. Finch was just smiling, a man full of glee,
For he had just left Sue, Miss Blue on Tier 3.
He learned something small, but learned it quite quick,
”This PowerShell thing is really quite slick!”
He learned how to turn two names into one,
And discovered it all, it felt so much fun.
So ventured he now, along that shiny Blue floor
To the chambers of the wizards that managed Tier 4.

On the way further down, he managed to see
A gaggle of Blues that were chanting so free,
Singing PowerShell songs, carols into the night,
Blueville IT workers, oh what a sight.
They sang high like the gerbils, shrilly into the air,
Oh their singing it was horrid, but they just didn’t care,
For PowerShell made them full of passion and joy,
Every single Blueville worker, each girl and each boy.

So quietly down and with nary a squeak,
Mr. Finch snuck away from their loud noisy shriek.
He admired their passion and their love of the Shell,
But Oh, did their voices sound like a daemon from hell.

So soon Mr. Finch found and opened the door
To the wizards and masters within that Tier 4.
Awaiting for them after the buzzer he rang,
Soon creaking open, locks moved with a klang.

“Please stop just a bit, before you continue your crimes,
We see you are followed by something that rhymes,
It drives us all mad, it makes us go crazy,
This rhyming machine, it makes our mind hazy.
Please stop that dumb thing, or cover it now,
We’re begging you please, don’t have a cow.”

But before he could stop it and get them some peace,
the batteries fell out and rolled down a crease.

“Oh thank goodness! I do not know who keeps bring these stupid RhymeOMatics in here! They’re driving us nuts!”

Mr. Finch looked up into the air, whistling quietly. Bad idea he approved from the social committee. He was not fessing up today.

“So! I must ask good gents what are your names?

Two hands burst out. “Hi my name is Lou!” the first mouth announced.

“…and I am Bo….”

Mr. Finch interrupted him. “Boo? Is your Name Boo?”

The second voice shook it’s head. “Uhhh no. Bouregard is  my name, but if you like, you can call me Boo. I kinda like that for a nickname.” He smiled, “Boo Blue…cool!”

“So how can we help you, our tortured soul of the rhyme?”

Mr. Finch quickly explained how he needed to import a list of users in a CSV file, how he had exported it from an old Active Directory, and then cleaned it up to just having the First and Last names.

“Sue Blue showed me how to build those names to match my Login account format, but at this point, I need to know how to pull that list in.”

Lou looked up. “Well you remember you used Export-CSV to pull the data out? There is another cmdlet called Import-CSV to read the data. To read your data, we just type in…”

IMPORT-CSV C:\Imports\Users.csv

Mr. Finch was impressed as he watched all the data flow by from his file. That part was remarkably easy.

“And now we’ll show you how to step through that information. We’ll store it all away in a Windows PowerShell variable called $UserList like this:”

$UserList=IMPORT-CSV C:\Imports\Users.csv

Mr. Finch was puzzled for a moment. “I’m a little confused. How can a Windows PowerShell variable hold something as simple as a First name or as complex as that entire list with no changes?”

Boo Blue stepped in. “That’s because all information in Windows PowerShell is an object. To you, you just saw the name—but to Windows PowerShell, it was an object that had not only a length, but content and methods available to it. Windows PowerShell doesn’t care about what an object has, just that everything is an object. Do not worry too much about them now. My head spun around about three times when I first tried to think too hard about an object.”

Mr. Finch nodded. After he learned how to import the users, he would look deeper into this whole “object” thing.

“So!” Lou popped in “We have your entire file in the Windows PowerShell variable $UserList. If we wish to step through this list, we simply use the ForEach-Object cmdlet. There are two ways that we can do this. One is to pipe the information directly into ForEach-Object like this:

$USERLIST | FOREACH-OBJECT { $_ }

“This will echo each entry in the list to the screen.”

“So what is that funny Windows PowerShell variable? The one with the underscore for a name?”

“AhHa! Glad you caught that!” piped up Boo. “That’s one of the many built in automatic variables that Windows PowerShell has. It contains the current object in the pipeline. As we stepped through the information in $UserList, this holds the entire content of that line. Or we can do it this way:

FOREACH ($Person in $UserList) { $ Person }

“In the second instance (using the $Person in $UserList setup), the variable $Person replaces the automatic variable, and it gives us something far more readable. Both work equally well, but I prefer the latter for readability. So if you’d like to access each entry in the list, we reference the names of the columns in your CSV file like this:”

FOREACH ($Person in $UserList) {
$Person.FirstName
$Person.LastName
}

As he watched the names roll by, Mr. Finch commented “I noticed that you moved the parentheses apart. Can they be on different lines?”

Boo jumped in, “Yes, they can sir. The Windows PowerShell parser looks for certain characters to tell it where the beginning and ending of a script block is. When it sees the first parentheses, it says ‘AhHa! Code is starting here!’ and it presumes that everything is code until it finds the opposite parentheses.”

“Ahhhhh….” Mr. Finch suddenly lit up like a light bulb. “Hey! Does that mean I can work with this variable the same way Sue Blue showed me? For building names?” He quickly grabbed the script Sue had him save and opened its content.

$Firstname=’Mister’
$Lastname=’Finch’
$Username=$Firstname+$Lastname.substring(0,1)
$Username

“So if I put these changes into your Windows Powershell script block like this…”

FOREACH ($Person in $UserList) {
$Person.FirstName
$Person.LastName

$Username=$Person.Firstname+$Person.Lastname.substring(0,1)
$Username
}

As he ran the script and he saw the names and account names flow down the screen, Mr. Finch felt a revelation. “Scripting really isn’t all that hard! So if this is all we need to do to get the names, how do we create a new user? I remember reading up on using New-QADUser. I got help by using the following script:”

GET-HELP NEW-QADUSER –examples

“…and when I ran it using this command line…”

NEW-QADUSER –firstname John –lastname Smith –userpassword ‘TempPass1’ –ParentContainer ‘Blueville.local/Division/Contoso’

“I found information like the SamAccountName and my UserPrincipalName weren’t there. Did I do something wrong?”

“Nope, you just needed to give it a little more information. You have to build your UPN for the Quest cmdlet. It will typically be in the format of username@domain.local. To find the @domain.local for your system, pull up an existing user account and look for the part after @. When you have that, you can modify your script to do the work for you.

I know that our domain is@blueville.local, so to have the script do that work, we make this change. Then we run the New-QADUser cmdlet with the proper parameter. Your entire script with the original import added would look like this.”

# Import list of Users From CSV into $Userlist

$UserList=IMPORT-CSV C:\Imports\Users.csv

# Step through Each Item in the List

FOREACH ($Person in $UserList) {

# Build Username from First name and Last Initial

$Username=$Person.Firstname+$Person.Lastname.substring(0,1)

# Put our Domain name into a Placeholder, who wants all that typing?

$Domain=’@blueville.local’

# Build the User Principal Name Username with Domain added to it

$UPN=$Username+$Domain

# Create the Displayname

$Name=$Person.Firstname+” “+$Person.Lastname

# Create User in Active Directory

NEW-QADUSER –FirstName $Person.Firstname –Lastname $Person.Lastname –Name $DisplayName $Name –SamAccountName $Username –UserPassword ‘1NewPassword’ –UserPrincipalName $UPN –Name $Name –ParentContainer ‘Blueville.local/Division/Contoso’

}

“Let’s save this as a new script called IMPORTNEWBLUE.PS1. Also, if you notice, there are a lot of lines in the script that start with a hash character. Those are all comments to help us remember why and how we did what we did. To execute the script, we just run it as.”

/IMPORTNEWBLUE.PS1

Mr. Finch watched the console. The list of all the staff was being effortlessly imported into the system. A large smile beamed across his face. “Scripting is coooooooolllll!” A puddle of drool formed from his mouth.

Lou and Boo looked at each other and smiled. The power of PowerShell was already flowing into Mr. Finch, his teeth were turning blue. His eyes were lit up like theirs.

“WooHoo! Thank you Lou and Boo,” he burst out, patting each on the back. “Now I can’t WAIT to get back to work! You’ve saved me hours of time!”

Mr. Finch spun about and danced out the door skidding across the hall towards his office.

“Look out world here I come!”

What a shout and a hoot came about in the air,
Mr. Finch full of joy and he hadn’t care.
For so much work in such simple way
He just couldn’t wait for PowerShell next day.

That is all for Part 4. Please join us tomorrow for the finale at Blueville.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Dear Sean,

    as I can see from this episode of Blueville

    Mr. Finch's problems habe been a thrill.

    But now, the battle is nearly won.

    Just execute the script ... and you are done!

    There's one error in the line starting with "New-QadUser":

    –Name $DisplayName $Name

    won't work! "$DisplayName" is not a defined variable!

    I suppose you wanted this:

    -DisplayName $Name

    Right, Mr. Blue?

    Another little note:

    I would move the line

    $Domain=’@blueville.local’

    before the Foreach-loop because it is loop invariant!

    In fact I rewrote the whole script a little bit like that:

    # Import list of Users From CSV into $Userlist

    $UserList=IMPORT-CSV C:\Imports\Users.csv

    # Some user (loop) independent values

    $Domain=’Blueville.local’

    $ParentContainer="$Domain/Division/Contoso"

    $UserPassword=‘1NewPassword’

    # Step through Each Item in the List

    FOREACH ($Person in $UserList) {

       $Username=$Person.Firstname+$Person.Lastname[0]

       $UPN     ="$Username@$Domain"

       $Name    =$Person.Firstname+” “+$Person.Lastname

       # Create User in Active Directory

       NEW-QADUSER –FirstName $Person.Firstname –Lastname $Person.Lastname `

           –DisplayName $Name –SamAccountName $Username –UserPassword $UserPassword `

           –UserPrincipalName $UPN –Name $Name –ParentContainer $ParentContainer

    }

    And even now, I would have to add error handling to it, at least for the "Imports-Csv" and "New-QadUser" statements!

    Klaus.

  • Klaus....

    Ah! the vile typo!  More evil than a Thousand "Rhymo-O-Matics" ! O

    Thanks )

  • sjeesus is writing a normal explanation really that hard? My head hurts from reading this!

  • Hi!

    I'd like to read .csv file but I cannt get column value.

    Classes.csv have  two columns: "class" and "computername"

    code:

    $path = "c:\fso\Classes.csv"

    $csv = Import-csv -path $path

    foreach($line in $csv)

    {

     write-host $line.class + "--->" + $line.computername

    }

    the code returns rows with "--->" only.

    whats wrong?

  • Also, you should probably show that you loaded the Quest Snapin, and how to Add Users using native PowerShell New-AdUser, its a lot more fun. Also, using New-ADUser enables us to deliver scripts that are always going to work without installing a third party extension. Or you should show how to enumerate the snapins then load the Quest snapin or download, install, and load so it can all be completed without end user intervention. You may have this information in a previous post.