Some problems only reveal themselves at scale. For instance, the get-msoluser commandlet only returns the first 1000 results, a problem that most 999...
Working With CSVs
February 1, 2017
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.
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.
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...