PowerShell Export-Csv

PowerShell Export-Csv with Get-QADUser

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.

Topics for PowerShell Export-Csv

 ♣

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.

Example 1: Getting to Know PowerShell’s Export-Csv cmdlet

# 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 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)

Example 2: Combining QADUser with Export-Csv

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

Example 3: Filtering users’ properties with select-Object

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. 

Review SolarWinds Free CSVDE Export Tool »

Summary of PowerShell Export-Csv

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

 


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.