PowerShell Import-Csv

PowerShell’s Import-Csv with New-QADUsernew_QADUser Import-Csv

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

 ♣

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.

# Research PowerShell’s import-Csv cmdlet
Get-Help import-Csv -full

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.

 Get-Command | Where {$_.name -Match "QAD"}

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 ToolFree Download Solarwinds 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.

If you need more comprehensive application analysis software,
Download a free trial of SAM (Server & Application Monitor)

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

# PowerShell script to list Active Directory users in a named OU
# Author: Guy Thomas
# Version 1.2 August 2008 tested on PowerShell v 1.0

$OU = "YourDomName/YourOu"
Get-QADUser -SearchRoot $OU

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

Stage 4: Researching New-QADUser

 Get-Help new-QADUser -full 

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.

  Name  sAMAccountName
  Bush  gbush
  Obama  bobama 
  Clinton  hclinton

 

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)Review of Orion NPM v11.5 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 UsersCreate new user PowerShell

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.

# PowerShell script create users in a named OU
# Author: Guy Thomas
# Version 2.4 August 2008 tested on PowerShell v 1.0

$OuBorn = ‘OU=PowerShell,DC=cp2,DC=Mosel'
$Freshmen = ‘E:\powershell\QAD\bunch4.csv’
import-csv $Freshmen |`
where {new-QADUser -ParentContainer $OuBorn `
-name $_.name -sAMAccountName $_.sAMAccountName}

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 ToolFree Download Solarwinds 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.

If you need more comprehensive application analysis software,
Download a free trial of SAM (Server & Application Monitor)

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:

# PowerShell script create users in a named OU
# Author: Guy Thomas
# Version 2.4 August 2008 tested on PowerShell v 1.0

$OuBorn = ‘OU=PowerShell,DC=cp2,DC=Mosel'
$Freshmen = ‘E:\powershell\QAD\bunch4.csv’
import-csv $Freshmen |`
where {new-QADUser -ParentContainer $OuBorn `
-name $_.name -sAMAccountName $_.sAMAccountName `
; enable-QADUser $_.name }

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.

If you like this page then please share it with your friends

 


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.