This page is where PowerShell meets CSVDE, any knowledge of CSVDE and its switches
is helpful in understanding how to use Export-Csv. Even if your ultimate goal is to bulk-import
accounts into Active Directory, there are sound reasons to master Export-Csv
first. For instance, exporting user objects is safer than importing new
accounts.
One bonus with both import and Export-Csv is that, you learn about the LDAP
properties of the Active Directory objects.
Our learning progression, is to master Get-QADuser, then export the output
into a .csv file.
# Research PowerShell Export-Csv
Get-Help Export-Csv -full
Note 1: The key conclusion of this basic research is that
Export-Csv requires a path. When we employ this cmdlet for real, we must include
a parameter to tell PowerShell where to store the
output.
Preparing the stream of users with Get-QADUser
Let us sort out the $Variables. I like using variables and they are particularly
useful in my examples, because they draw attention to the particular values that
you must edit in order that my modified script works on your system.
Where are your users! Mine are in "OU=YourPeople,DC=cp5,DC=mosel",
therefore change this line in your script. OU obviously means
Organizational Unit, but DC means Domain Context, and not domain controller.
You can find the correct domain name in your Active Directory Users and
Computers.
# PowerShell script to list users in a named OU
$OuDomain = "OU=YourPeople,DC=cp5,DC=mosel"
Get-QADUser -searchRoot $OuDomain
Note 1: Another useful feature of Get-QADUser is the parameter -searchRoot.
This works somewhat like dir /s, in that with -searchRoot the cmdlet 'Get-QADUser' drills
down into child OUs.
Challenge 1: You could substitute -searchScope 'OneLevel' for -searchRoot.
Other values for -searchScope are 'Base' and 'SubTree'. If you need more information try,
help Get-QADUser -full
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.
Where should your .csv be created? Mine will appear magically at
"c: \YourDir\people2.csv", however, you need to edit the $FilePath variable in
this script. Naturally, you also need to amend the value for $OuDomain, as you
did in the previous example.
# PowerShell cmdlet to export active directory into a csv file
$FilePath = "c:\YourDir\people2.csv"
$OuDomain = "OU=YourPlace,DC=cp5,DC=mosel"
Get-QADUser -searchRoot $OuDomain | Export-Csv $FilePath
Note 1: One of PowerShell's most useful constructions is the
pipe (|). This enables the output of Get-QADUser to become the input of
Export-Csv.
Note 2: While you can read the .csv file in notepad, I find that a
spreadsheet such as Excel much more convenient for displaying the columns of data.
See more examples of PowerShell Export-CSV
Did you suffer from information overload in the previous example? If so,
you can reduce the number of LDAP fields by adding a search-Object command
which lists just the properties you are interested in, each separated by a
comma. Actually, the previous example provides an excellent chance to
research the users' properties by studying the first two rows of the exported .csv file.
# PowerShell cmdlet to export active directory into a csv file
$FilePath = "c:\YourDir\people3.csv"
$OuDomain = "OU=YourPlace,DC=cp5,DC=mosel"
Get-QADUser -searchRoot $OuDomain `
| select-Object name, SamAccountName, UserPrincipalName `
| Export-Csv $FilePath
Note 1: The tiny backtick ` symbol tells PowerShell that the
command continues on the next line. In other words, (`) means
word-wrap.
Export-Csv comes into its own when you want to save information about Active
Directory users. It is also a safe way of preparing for Import-Csv, which
in turn, is a convenient way of creating a large number of users, computers, or groups from a
spreadsheet.
If you like this page then please share it with your friends
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.
Windows Management Instrumentation (WMI) is
most useful for PowerShell scripting.
SolarWinds
have produced this
Free WMI Monitor to take the guess work out of which
WMI counters to use for applications like Microsoft Active Directory,
SQL or Exchange Server.
