One of my favorite ways to apply a change across a wide number of users or items is the use of an imported CSV. At first the process seems a bit intimidating but once you get the basic principles down you’ll be turning to CSVs for everything from changing usernames to creating images of Azure VMs.
The Basics
Before we dive into the syntax of the PowerShell script lets first look at creating the ideal CSV. Excel will be your best friend for creating, editing, and export CSVs. I’ve found that while it is not required that your CSV have headings, it will make your life easier. To take this a step further I would suggest that where possible use headings that match the name of the variable you’re looking to use that column for.
In this example we’re going to be creating 4 distribution groups. The PowerShell command for creating a distribution group is...
New-DistributionGroup -Name Desired Name -DisplayName Desired Dsiplay Name –Alias Desired Alias –PrimarySmtpAddress Desired Primary SMTP Address
This results in our CSV in excel having the following headings
After entering in our desired rows for the lists we want to create it looks like so...
One reason why I prefer to use Excel to create my CSVs is that it makes exporting a breeze. Highlight the data you’d like to export, for this example that would be A1:D5, and then select the export command from the file menu.
Be sure to save the CSV to easy to find folder, I create a folder named PS in the root of the C: drive to consolidate all my various scripts and CSVs.
Baking a Script
Just like a turkey but with less basting
CSV in hand it’s time to whip together our script. In short our script will need to import our CSV and assign it to a variable which will become an array of our data, loop through each row in our array, and execute our desired command(s) as it loops though each row.
I’ll break down each line and its purpose and then we’ll put it all together
$DistLists = Import-CSV c:\PS\DistLists.csv
This line represents the creation of our 100% arbitrarily named array of data, DistLists, the $ simply tells PowerShell this is a variable. We then set the value of DistLists to the result of the import of our CSV that we save in c:\PS\
ForEach ($DistList in $DistLists)
After punching in a ForEach we introduce yet another variable, DistList. Anytime you import a CSV you have 2 variables. The first variable is for the array of data resulting from your imported CSV and the second variable represents a single row of that array.
Please do not get hung up on the names of these variables, I could have easily used bananas and oranges as variable names. The plurality or singularity of these variable names does not matter.
{
This squiggle (no I will not call it by its correct name) represents the start of what we want PowerShell to do with each DistList in DistLists or with each bananas in oranges.
New-DistributionGroup -Name $DistList.Name -DisplayName $DistList.DisplayName –Alias $DistList.Alias –PrimarySmtpAddress $DistList.PrimarySmtpAddress
Beautiful isn’t it? As our ForEach command cycles through each row in our CSV we can reference each column by calling $DistList.(Heading Name) or $bananas.(HeadingName). Hopefully you find this validates my decision to use matching heading names.
}
This squiggly represents the end of what we want PowerShell to do with each DistList in DistLists, it also represents the end of this script.
When we put together all the pieces we have the following...
$DistLists = Import-CSV C:\PS\DistLists.csv
ForEach ($DistList in $DistLists)
{
New-DistributionGroup -Name $DistList.Name -DisplayName $DistList.DisplayName -Alias $DistList.Alias -PrimarySmtpAddress $DistList.PrimarySmtpAddress
}
Now let’s play a game of “What If”. If you were of fan of chaos and fruit your CSV would have looked like this.
And your script would have looked like this AND it would have functioned just fine.
$Orange = Import-CSV C:\PS\DistLists.csv
ForEach ($Bananas in $Orange)
{
New-DistributionGroup -Name $Bananas.Cat -DisplayName $Bananas.Donkey -Alias $Bananas.Propane -PrimarySmtpAddress $Bananas.Dogma
}
The Wrap-up
That’s all there is to it. Many of our posts will use imported CSVs to speed along the input process so I hope this explanation didn’t leave you more confused than before.
As always please feel free to reach out to me if you have any questions or suggestions for future post topics.
