Powershell: Working with CSV Files

Today I'm going to talk about interacting with CSV files using powershell.  You may use CSV files to store values temporarily for a script, or you may be creating user accounts in Active Directory.  I'll be going over the process of how to read the file and declare variables for the headers.

The root of the whole process is importing a CSV file you've created.  The command to do this is:

Import-Csv pathtocsvfile

So that reads the file but how do we interact with the data?  The following example shows how you can process a csv file one row at a time.  This script simply echoes the data stored in the 2 columns in the csv file.

 $testcsv = import-csv c:\scripts\test.csv
  
 
  
 foreach($test in $testcsv)
  
   {
  
   $field1 = $test.field1
  
   $field2 = $test.field2
  
   
  
   Echo "$field1, $field2"
  
   }
  
     

So to break this out piece by piece:

1. This imports the csv file and stores it in the variable $testcsv

$testcsv = import-csv c:\scripts\test.csv

2.  This starts a "foreach loop" to process the csv file row by row.  The first variable, $test, can be named anything you want it to be.  I find it good practice to name it something relevent to the import-csv variable.  The second variable is the one we defined earlier for importing the csv file.

foreach($test in $testcsv)

3. Start the loop with a {

{

4.  This part is how we tie variables to the column headers in the csv file.  Again, name these what you want.  I like to name them after the column headers.  Set them equal to the $test variable dot (.) the column header they are tied to.

$field1 = $test.field1
$field2 = $test.field2

5.  This is the command we are going to run each row through.  Use the variables from the last step in the command.  Here we are telling powershell to echo field1 and field2.  Again, each row in the csv will run through this command.

Echo "$field1, $field2"

6.  Close the foreach loop with one of these }

}


My csv called test.csv looks like this:

Field1,Field2
data1,data2
data3,data3

When I run this script in powershell, I see the output of rows 1 and 2:

Create Active Directory Accounts from a CSV file with Powershell

Here's another example of how you can create Active Directory accounts using this method.



 ##########################################################################
  
 #     Create Active Directory Users from CSV
  
 #
  
 # This script will allow you to import users from a csv file
  
 # and create an active directory account for each of them. This requires
  
 # your domain and forest functional level to be 2008 R2.
  
 #
  
 # Source: ps1scripting.blogspot.com
  
 #
  
 ##########################################################################
  
 
  
 
  
 #Imports active directory module for running AD cmdlets
  
 Import-Module active directory
  
 
  
 #variable to store the data in adusers.csv
  
 $adusers = Import-csv c:\scripts\adusers.csv
  
 
  
 #This will process the CSV row by row. Each row contains information to create an active directory account for a user.
  
 foreach ($user in $adusers)
  
   {
  
   
  
   $username = $user.username
  
   $password = $user.password
  
   $firstname = $user.firstname
  
   $lastname = $user.lastname
  
   
  
   #create AD Acct #sets for username  #sets account name      #sets first name   #sets lastname  #enables account #sets display name        #sets login script path
  
   new-aduser -samaccountname $username -name "$lastname, $firstname" -givenname $firstname -surname $lastname -enabled $true -displayname "$lastname, $firstname" -scriptpath "\\server\loginscript.bat" -accountpassword (convertto-securestring $password -asplaintext -force)
  
   
  
   }  

I like to automate the Active Directory account creation process by using SQLCMD to select users from a SQL database and output them to a csv file.  Set these scripts up as scheduled tasks and let powershell do the rest!


Disclaimer: All scripts and other powershell references on this blog are offered "as is" with no warranty.  While these scripts are tested and working in my environment, it is recommended that you test these scripts in a test environment before using in your production environment.