Up next in 10
PowerShell ForEach and CSV File Tutorial
12K views
Mar 26, 2024
Harness the power of the PowerShell foreach statement with CSV files to perform a repeated action on some data, like creating users! PowerShell ForEach and CSV Files: Tutorial https://jeffbrown.tech/powershell-foreach-csv/ Chapters: 00:00 Introduction 00:22 CSV File Overview 01:01 Import-Csv Examples 03:13 Imported Data Manipulation 05:36 foreach Statement 11:17 Script Example 14:11 Summary
View Video Transcript
0:00
What's up everyone? Today I want to talk to you about how you can use the for each statement with CSV files to
0:07
input data into your scripts or commands that you're working with in order to go through and process a lot of data
0:13
which is what PowerShell scripting is perfect for. This video is based on a
0:17
blog article that I wrote that I will link to down in the description
0:21
Let's get started by taking a look at VS Code here. On the right, I have an example CSV file
0:27
It has three columns, first name, last name, department, and I have a couple of items here in the list of people's first and last names and their department name
0:38
If you're not aware, CSV is comma separated values and as you can see each value in my
0:45
file here is separated by columns. I also have an extension VS Code called Rainbow CSV that color codes each column inside the CSV file
0:54
just so when you're looking at the file, you can see which column aligns with which values
1:00
The first thing we want to do is we want to import this data
1:05
and view it inside of PowerShell and we can do that using the import CSV command
1:10
There is a parameter path and we give it a path to the file that we want to import
1:16
Here I have the path to this file that we're looking at here on the right. Let's just go ahead and run this real quick
1:21
You can see it's imported the CSV file. It's kept our column names
1:25
We have our header there, first name, last name, department, and then each of our items inside of the file there
1:32
This is pretty straightforward. We have a file. We can import it, view the data here. Perfect
1:37
Let's take a look at another example of a CSV file. This one does not have that header row
1:42
so we don't have each column name. It just has the data. You can still import this
1:47
What you can do is first create a variable here. You see it on line 8 called header
1:53
We'll put in each name of our columns that we want. We have first name, last name, department
1:58
each closed in quotes and separated by commas. You can even have spaces in between the comma and the item there
2:05
Let's go ahead and import our header variable here. Then we can go ahead and run our import CSV command again. We're going to give it the file without the headers
2:16
There's an extra parameter called dash header and we will pass it our header variable that we just created
2:21
We run that. We get the exact same output. We have our header and our data inside of it
2:26
Let's go ahead and clear the screen for the next example. Like I mentioned, import CSV is assuming you are importing a file that is comma separated
2:35
But if you have a file that is not using commas, you can
2:41
indicate the delimiter using the dash delimiter parameter. In this case, let's go look at another example
2:47
Notice we still have our headers in there, first name, last name, department, but everything's separated by a colon for whatever reason. We'll run the import CSV command, specify our new delimiter of the colon
2:58
Go ahead and run this and we get the exact same result. Just showing a couple of ways you can work with CSV files, import them into PowerShell and work with the data
3:09
In fact, working with the data, let's move on to the next example. Right now, we've just been importing the data and displaying it to the screen
3:16
but you can import that data and save it to a variable. In this case, that variable is going to be user data
3:23
We'll put equal sign and we'll go back and import our CSV file here
3:27
And then we can output the result of that or the value of the variable
3:33
which is exactly what we've been seeing when we just import it without saving it to a variable
3:38
Now, the thing with this variable that we now have that has all of our data stored in it
3:42
is it contains each item in our list or in our file
3:47
and each column name is now a property. It's a custom object. So if we run this against get member
3:55
notice the last three entries here. We have department, first name, last name, which lines up with the columns we have in our file
4:00
And each of those are a note property or just a property and it's typed or strings
4:06
for each item in our array here or collection. And we can access these different properties just using dot notation
4:14
We can say let's look at all of the user data and our list of departments
4:18
We have a list of our four different departments there. Same thing with first name. We can look at everyone's first names and output that to the screen
4:26
Along with this, after importing and saving it, we can now do filtering on those different properties
4:31
Maybe I want to look at everyone whose department equals accounting so I can take user data
4:37
pipe that to the where object, and put in my filter clause there
4:42
And as we can see, that is just Maggie Smith. As we just mentioned, user data has all the data that's inside of our file
4:50
Maybe you want to just work with a specific item in that collection or array and you can use this using the square brackets
4:58
and using indexes. All these arrays and collections, they start at zero
5:03
So if you want to view just the first item in there, you can do bracket zero
5:07
If we run that, it pulls back my information and that's the first one in the file here that we have
5:13
You can also view a range, maybe the first item to the second item. That's Jeff and Maggie's data here
5:20
One really cool thing is you can do negative one, which will get you the last item in the array or collection
5:28
which is maybe super helpful. You want to see what was the last item maybe you added to it
5:32
If you do negative one, we can see that is Mike Simpson. Let's move on to talk about the for each statement. This allows you to loop through or iterate through a collection
5:43
The basic syntax is for each and then inside parentheses we have item in collection
5:50
In this case, the collection variable has already been created. It should already exist
5:56
The item variable here is one that you define. You can call it whatever you want. You don't have to define it beforehand
6:02
But that dollar sign item or what's in the first part of the end statement
6:07
is going to define how you access each item in the collection or array
6:12
Then you have square brackets and you perform whatever actions you want to perform on this data
6:18
Usually what I'll call the collection variable should be plural or maybe it should be called something like all numbers
6:25
Then your singular item there is dollar sign number to indicate the one item that you want to work with throughout the collection at a time
6:33
You can technically call the first part of this in here whatever you want
6:37
It could be for each hot dog in all numbers or for each sandwich in all users
6:43
But in general, you probably want it to make sense within your script. In this case, we have number in all numbers
6:50
for each user in all users. Let's take a look at an array here called all names. We have some first names in here
6:59
and then we have for each name in all names. So that makes sense. We're going to iterate through and work with each name in all names
7:06
That's how you reference the current item that you're working with. In this case in our statement in the for each
7:12
we're just outputting to the screen. What is the current item that we're working with? That is going to be dollar sign name. So let's create
7:20
our all names variable here and we'll run a for each loop. You can see it has done current item Ted, Rebecca, Patrick, June
7:27
It has iterated through each of those items one at a time. We access each item in the collection using the variable name at the first part of the end statement
7:36
You can also perform different actions depending on what that object is. In this case, it is a string
7:43
So we're going to go through and say for each name in names. We'll output that person's name has and we'll say name dot length
7:51
That will give us how many characters or the length of that string. You'll notice here
7:56
we have dollar sign name dot length that is enclosed in parentheses
8:01
and then another dollar sign outside of that. We'll talk about what that expression is here in just a second, but let's go ahead and highlight our for each here
8:11
And it outputs Ted has three letters, Rebecca and Patrick each have seven and June has four letters
8:17
So just showing how you can use the dollar sign name, you know, the individual
8:22
identifier there for your collection and perform different actions on it. As we just saw in the last example, you can access different properties within that imported csv data
8:31
And we also saw in that example how you can access those properties if you're putting them inside of a string for
8:38
string interpolation. You also saw how you had to use a sub expression. That's the dollar sign parentheses
8:44
to access those sub properties inside of a string for string interpolation
8:50
Again, we'll take a look here. We're going to say we're going to re-import our user data and say for each user and user data
8:56
we'll output their first and last name works in this department and we had to use sub expressions to access each of those properties there
9:04
So we'll make sure have the latest user data from our csv file and we'll run our for each and again
9:09
just outputting a sentence here accessing each of those different properties inside the string
9:14
If you're going to be working with those properties quite a bit, it may be more useful and quicker to type out if you save each of those to their own variable
9:23
Line 68, we're taking dollar sign user dot first name. Here you don't have to put it in the sub expression of the dollar sign parentheses
9:30
and we're saving it to its own variable first name and then doing the same thing for last name department
9:35
And then just putting those inside of our string. If we rerun this, that should give us the exact same output we saw a second ago
9:42
Just showing you can take those properties inside your csv file and save them to their own variable and then reuse that variable inside of your script here
9:53
One trick I recently learned is let's say you are working with a large data set
10:00
Let's say the csv file had a hundred users information in it
10:05
and you're writing a script and you want to test it out, but you don't want it to run through all 100
10:09
Maybe you just want it to run through a subset of those users while you're testing and figuring it out
10:15
Remember earlier, we took a look at our indexes on how to access specific items inside of the array or collection
10:22
You can do the same thing in a for each statement. So here we still have our for each statement for each user and user data, but notice here we have
10:32
user data and we're just saying give me the first item inside of user data using the zero index
10:38
This means it is only going to iterate through that first item you see inside of your collection
10:45
If we rerun this, it should just output my name and department
10:50
There we go. And it does that because we limited here on line 75 to only look at the first item inside of the collection
10:59
Again, we can modify that to say the first one and two here or the ones at index zero and one that's going to output Jeff and Maggie's information
11:09
Just a great quick way to modify a script in order to only work with a subset of those items
11:17
All right, now that we've gone through the basics of a csv file and using for each loops and working with the data inside
11:23
a csv file, let's see how we can actually use this inside of a script
11:27
to create new users out in our intra ID or Azure AD environment
11:34
I have a small script here. We'll go through some of these components just real quick
11:39
I'm defining what scopes I want to connect to Microsoft Graph with here on lines one and three
11:45
Then line five, I'm going to import the csv data again and assign it to a variable
11:50
This is what we saw earlier. Then I have my for each loop of what I want to do as I go through each person's data
11:58
I'm going to generate a password here on line eight through 13
12:02
I'm just generating a random number and appending that to the word change me
12:07
Then I'm creating some variables, male nickname, display name, using properties from the csv file using our sub expression here. We saw that also, so first name last name
12:19
Then line 18 through 27, I'm using splatting to create the parameters for my upcoming command to create the user
12:29
I'm setting different parameters using display name and our variable that we just created. Then we're using given name, surname, again accessing those properties here from our file
12:40
User, principal name, male nickname. We've got our password profile with our password
12:43
we just created, their department, and enabling the count. Then finally on line 29, we run the new mg user, which is going to create a new user using the Microsoft Graph PowerShell module
12:55
Then at new user params is just referencing the hash table created on line 18
13:01
which contains all of the parameters that we want to use and their values for this command. Then at the very end, I'm going to disconnect from my Microsoft Graph
13:12
Ideally, if I run this, it will go through and create each of our users inside our example CSV file
13:22
Now that we've got everything how we want it, let me go ahead and run our PowerShell script here to create these new users
13:28
What you don't see off screen right now is I'm authenticating out to my Azure AD tenant. That's what the connect command should do
13:36
It looks like it ran through a couple of things. Let's see what it says here. All right, so it looks like it created our users here successfully
13:44
You can see how if you had a lot of users that you need to create, how you can take a CSV file
13:51
import it, and use it in a PowerShell script to do what scripting and programming is perfectly doing
13:56
Lots of same things over and over. We went through here and created our four users just like that real quick
14:03
Make sure they're made consistently. They've always got their first name, last name, the display name, user principal name
14:09
All those things are consistent. That is for this video. Hopefully this will help you in your scripting journey here
14:15
If we take a look at this script, there are some improvements we can make. For example
14:21
line five, we have our path to our file hard-coded. We could easily add that to a parameter and we could use some error catching, something I talked about in another video
14:32
In the next video, we'll go through how to make improvements on this script to turn that into a parameter and add some error handling
14:38
Thanks for watching and we'll see you next time. You
#Programming