PowerShell Import-Csv

PowerShell’s Import-Csv with New-QADUser

My mission is to create new user accounts from data held in a CSV file.  For this mission we need the following skills, execute PowerShell cmdlets, install QAD snap-Ins, research LDAP properties and control spreadsheet columns. 

Topics for PowerShell Import-Csv

• Stage 1: Introducing PowerShell’s Import-Csv cmdlet
• Stage 2: Checking the QAD snap-In
• Stage 3: Investigating Get-QADUser
• Stage 4: Researching New-QADUser
• Stage 5: Preparing the Spreadsheet
• Stage 6: The Master Plan to Actually Create Users
• Summary of PowerShell’s Import-Csv

 ♣

Planning for New Users

Whenever I plan a script which imports users into Active Directory, the very best preparation is to create a script which exports user accounts from the domain.  The benefit is that we learn the syntax and develop strategies in the knowledge that we are not flooding Active Directory with useless accounts.

While the QAD commands simplify the task of creating new users, it’s worthwhile spending time investigating the Get-QADUser cmdlet before turning our attention to the more powerful new-QADUser.

Pre-requisites, particularly for the QAD snap-In

Before we can get my examples to work you need to meet these pre-requisites.

1. Download and install PowerShell and .Net Framework. 
   Go to Microsoft’s site and choose the flavour to suit your operating system.
2. Download, then install the QAD Snap-Ins from this site:
   http://www.quest.com/activeroles-server/arms.aspx
3. Register the snap-In. (Key point)
   add-PSSnapin quest.activeroles.admanagement
4. Gain access to Active Directory.  Best would be to logon at a domain controller in a test domain.

Stage 1: Introducing PowerShell’s Import-Csv cmdlet

Let us investigate which parameters are required by the main, import-Csv cmdlet.

Note 1:  The most important conclusion of this research is that import-Csv requires a path.  When we employ this cmdlet for real, we must include a parameter to tell PowerShell where to find the .csv file.

Stage 2: Checking the QAD snap-In

Having met the pre-requisites in general, and added the snap-In in particular, let us now list the QAD cmdlets.

Note 2:  The two cmdlets of most interest are new-QADUser and Get-QADUser.

Note 3: See more on the $_ technique.

Guy Recommends:  SolarWinds’ Free Bulk Import Tool

Import users from a spreadsheet.  Just provide a list of the users with their fields in the top row, and save as .csv file.  Then launch this FREE utility and match your fields with AD’s attributes, click and import the users.

Optionally, you can provide the name of the OU where the new accounts will be born. Download your FREE bulk import tool.

Stage 3: Investigating Get-QADUser

This is an intermediate stage designed to build confidence and to gain a feel of QAD syntax.

Before you begin, it’s imperative that you find the variable $OU in my script(s), then amend its value to reflect your domain, and your Organizational Unit.  You many need a little extra work with Active Directory Users and Computers in creating an OU and a handful of users.

Review SolarWinds Free CSVDE Import Tool »

QADUser:  List Accounts in a Named OU

Note 5: -SearchRoot is the parameter which connects to Active Directory.

Stage 4: Researching New-QADUser

Note 6: The crucial piece of information that new-QADUser needs is: ‘Where are the users going to be created?’  The answer is held by the very first parameter -ParentContainer.  In the main script we will use $OU to hold the value of your domain and your Organizational Unit.

Note 7: -Name, well every object needs a name.  But after -Name comes a plethora of other parameters which look like LDAP properties.  This is a list to research when you need to populate the user accounts with optional properties such as ‘Manager’, ‘Company’ or ‘DisplayName’.

Conclusion:  I will produce a short list of the minimal properties that you need to create your first tranche of new users.  Let us start with just  -name and -sAMAccountName.  Once that works, we will be more ambitions and introduce properties such as: -LastName -firstname, -userPrincipalName and -company.

Stage 5: Preparing the Spreadsheet

We are going to store the user’s data in a spreadsheet.  The first row is crucial because here are the names of the properties.  To keep it simple and logical I will name them after the properties that we researched for new-QADUser, for example, name and sAMAccountName.

Each row in the spreadsheet holds the values for one user.  The final job for the spreadsheet is to save as a .csv file – and to remember that file path.

Note 8:  The first row holds the vital property names.  You could think of these ‘fields’, as LDAP properties, or import-Csv parameters.

Guy Recommends:  A Free Trial of the Network Performance Monitor (NPM) v11.5

SolarWinds’ Network Performance Monitor will help you discover what’s happening on your network.  This utility will also guide you through troubleshooting; the dashboard will indicate whether the root cause is a broken link, faulty equipment or resource overload.

What I like best is the way NPM suggests solutions to network problems.  Its also has the ability to monitor the health of individual VMware virtual machines.  If you are interested in troubleshooting, and creating network maps, then I recommend that you try NPM now.

Download a free trial of Solarwinds’ Network Performance Monitor

Stage 6 – The Master Plan to Actually Create Users

In the master plan we will employ the import-Csv cmdlet to read the spreadsheet.  Then we will feed in the values held in the columns, so that they become the properties of the new-QADUser.

Preparation:  Edit $OuBorn.  The value of ‘OU=PowerShell,DC=cp2,DC=Mosel’ will not work with your domain.

Where is the path to your spreadsheet?  Edit $Freshmen.

Note 9:  The two crucial cmdlets are: import-Csv and new-QADUser.  Observe how PowerShell pipes the output of import-Csv into new-QADUser.  Incidentally, (|) is an important method in PowerShell, learn it here and employ this pipe in future scripts.

Note 10:  I have introduced two variables, $OuBorn and $Freshmen.  To be brutally honest, the script will not work unless you change the values held by these $Variables to reflect your environment.

Note 11:  $_. is a special PowerShell variable which says, ‘In this stream’,  hence $_.name means use the name column from the spreadsheet.  I hope that you can now see the importance of the first row in your .csv file.

Note 12:  Observe the backtick (`).  Perhaps the best way of seeing what this word-wrap symbol achieves is to remove it, and type the command all on one line:

import-csv $Freshmen | where {new-QADUser -ParentContainer $OuBorn  -name $_.name -sAMAccountName $_.sAMAccountName}

Guy Recommends:  SolarWinds’ Free Bulk Import Tool

Import users from a spreadsheet.  Just provide a list of the users with their fields in the top row, and save as .csv file.  Then launch this FREE utility and match your fields with AD’s attributes, click and import the users.

Optionally, you can provide the name of the OU where the new accounts will be born. Download your FREE bulk import tool.

Where Next with PowerShell’s Import-Csv?

My speciality is getting you started.  Once you can create a basic user, then it will be a labour of love to research more properties to include in your spreadsheet.  For example, if you have a real-life task of creating dozens of users, then adding values for LastName, and UserPrincipalName would result in better users!  See more examples of PowerShell Import-CSV

Two more things that you might like to do with your users

1. Give them a password and insist upon UserMustChangePassword
2. Enable the account by appending:
   ; enable-QADUser $_.username  (Note you need the semi-colon)

For example:

Tip: Try an Export-Csv for a user that you created manually in Active Directory User and Computers.  Then research the LDAP names in the first row.  Naturally, the more property boxes that you add information, the more sense that you can make of the resulting export.

Summary of PowerShell’s Import-Csv

In order to import user accounts into active directory you need to be multi-talented.  In addition to PowerShell skills, you need to be a veteran of spreadsheets and a professor of LDAP properties.  What makes a difficult task achievable is the QADUser object which you obtain with the QAD snap-In.

See more PowerShell QAD Scripts

• PowerShell Home   • Quest QAD   • QADUser   • QADGroup   • QADComputer

• Export-CSV   • Import CSV   • QAD Password   • Add-PSSnapin   • Free Import User CSVDE Tool

• Get-AdUser -filter   • Windows PowerShell   • Windows PowerShell .Net

Please email me if you have a better example script. Also please report any factual mistakes, grammatical errors or broken links, I will be happy to correct the fault.