PowerShell 7 Tutorials for Intermediates #6 : CSV Files

hi and welcome to the sixth video in our Powershell 7.24 intermediate tutorial Series in this video we're going to be taking a look at how to interact with CSV files so comma separated value files you're going to probably deal with a lot of these types of files and XML files and Json files which we're going to look in uh the next few videos as well but you're probably going to be dealing with a lot of these files from your HR department or from other software often will export into a CSV format and you can use Powershell to parse them and just filter the data quite easily and be able to create some nice reports for Managers from active directory or even from some different services so let's go ahead and let's actually take a look at some CSV files with Powershell so I actually created uh two CSV files before this filming of this video so I have this CSV file with a bunch of employees that are comma separated I also have the same file but we just separated them by semicolon uh just to show you guys the different ways of using delimiters and you're not limited to only comma delimited files you can use semicolon delimited files you can use tab delimited files I'm going to go over those different different options for you so let's actually first go ahead and get started on loading the data into Powershell so we've seen a commandlet to pull data from a file and we've seen that as get content so what we're first going to do is we're actually going to create a variable here to hold in our CSV file path so we're going to create a variable and we are going to open the comma separated value so let's just put in that file path here and then what we're going to do is we are just going to create another variable called content we're going to make that equal to the get content commandlet and we're going to do the path of our CSV file path so let's go ahead and let's run this and let's take a look at what is inside content so right now in content we see the contents of our file but it's not really usable now Powershell actually has a built-in commandlet for CSV files that you can import that data in very very nicely and you'll actually get properties and be able to Loop through the different data you could probably use this data if you manipulated it with some for loops and did some extra formatting to it but Powershell already makes that very very easy for you so let's actually erase this get content line um and we're just going to create a variable named called employee data and we're going to make that equal to import Dash CSV and we're going to specify the parameter path and we are going to put our CSV file pop in here and let's go ahead and let's run these two lines and let's take a look at what is inside employee data so here we have our employee data so as you can see we actually have our headers here ID name title and Country and we actually have our data here so this is actually very very nice the formatting all came in really good and what we can actually do with this afterwards is if we do an employee data dot get pipe here just so we can see what kind of type this variable is we can actually see that it is an array so it's not an arraylist what you could do is you could create employee data as an array list and then add in the import CSV and you would have an array list at that point but for the most part if you're just importing a CSV you're probably just going through the data you're probably not really going to be adding values to it or manipulating data directly in that CSV so it's probably pretty safe to just use as an array but if you do know that you're going to be modifying that data it might be a good idea to bring it in as an arraylist because you're probably importing a lot of data or you might be adding a lot of data into that so let's see what we can actually do with a for each Loop here so we've seen this in the beginner tutorial series because we have an array so we can actually say for each dollar sign employee in employee data here actually let me just corrupt this to be a lowercase e and then we have our open and close bracket now if we do a dollar sign employee dot notation we won't have anything that appears so what I actually like to do here is just leave that for each empty and if we run this here and we actually do a dollar sign employee dot we will actually have our DOT notation laid out for us so we actually already have all the different properties from our employee data type or our employee object so what we can actually do here we're just going to do a right output in this case but we're going to do a right output employee and then we're going to do a variable wrapper so that's just the dollar sign open and close parentheses let's do the employee Dot name um is a and then another variable wrapper here and we're going to put in their title and we are going to say they are from and then another variable wrapper and we're going to put in the country that they're from so we're bringing in quite a bit of data here and let's run this for each Loop so here we have like employee Jane Smith as a programmer from Canada and then we have all the other employees and it says their title and it says what they are from as well so that should actually be really really good now what you would use this in an example like a real life example is if you got like a CSV file like in this case it's an employee data file if you had something coming from your HR department or you have a software that manages your employees and you can get like an export in a CSV you can actually use this CSV to completely manage your active directory now I actually have a full tutorial and a full project on how to manage your active directory through a CSV file and you have it completely automated so there is that video you can definitely check that out on my channel and that will actually bring you from the beginning to the end all the way from having that CSV file the configuration of the CSV file creating the module to create users delete users and modify users because we'll actually check the name to see if there's been any name changes any position changes anything like that so importing CSV files is very very big in terms of Powershell now we saw how to import a comma delimited file but let's say we actually imported our file with the semicolon so let's do the semi-test here and if we run these first two lines and we actually look at employee data we will see that it doesn't actually look that good anymore we have our our header but we only have one header and they're separated by semicolons but they're all just one data so if we actually look at the for each Loop now it's actually broken it's not working correctly we have employee blank is a blank uh from blank so we don't have anything here what we actually have to do in the import Dash CSV is specify the parameter delimiter and there you can actually specify your delimiter so if we put the semicolon delimiter here and we actually run this again there we have it it actually Imports the data correctly now if you were trying to import let's say a tab delimited file what you would do is you would actually do a back tick and then the letter T and that would tell it that it is a tab delimited file and then it'll import that just as just fine as well so there are tons of different ways and tons of different uh types of dilemma that you could use I always even if it's just a comma delimited file I actually tend to use in all my scripts I will still put the delimiter there this way it's very easy to know okay it's expecting a comma delimited file it's specified in the script so if anyone goes and reads your script it is very easy to see what it is trying to do now we've seen how to actually import CSV files but the other very powerful thing in Powershell is we can actually export to a CSV so we can actually create comma delimited files from our data on the computer whether this be from active directory our services or any of the other things that you can interact with Powershell apis and the list goes on um forever basically what we could actually do here is let's use our commandlet that we use very often here to test things out which is our get service commandlet what we could actually do is we can actually export this into a CSV file so if we do a get service and all we want in our CSV file let's say is the name the display name the status and the start type the reason why we want the status and the start type is maybe we want to make sure that all the automatic Services all the ones that are supposed the startup automatically are started and make sure that none of them are stopped so let's do a get service here we're going to do a select object select Dash object and then a dash property and let's do name display name status and then status type so if we actually just run this line here we will actually see that everything kind of comes out pretty nicely it doesn't really come out nicely on this display here actually so if I just do another uh pipe here and we're just going to do that to a format Dash table that'll usually make it very nicely formatted for us with all the data that we want which actually is still not coming up here um but let's actually just pipe this over to export Dash CSV and what we're going to do is once we have that set to export CSV we're going to give it a parameter of path and let's put in that path here which is going to be C backslash scripts backslash for me it's going to be intermediate tutorials we're going to go into CSV files and we're going to name it test export Dash CSV and then we could give it the parameter of delimiter so let's make that a comma delimited file and then one other parameter that we're going to want to do is going to be the no type information and let's go ahead and let's run that here and once we actually have that we actually get our file back so we have our test export here and we actually have our full-blown um get service commandlet and the output of it with the status and the start type as well so we actually have all those different properties in there and what we could actually do as well is with this select object we can even do as we've seen we can put expressions in our select statements so if we do very similar to a script block here so we're going to put the at symbol and then a script block so this is going to be um like a hash table and then we're going to do a key value or a name value pair so let's do the name is going to be uh comma double quote time and then we're going to do a semicolon and we're going to put in the expression and for the expression it's going to be another script block so we're going to do a open and closed curly bracket here and for that we're just going to do a get date Dash format because we want it formatted in a specific way for us and we want it to be month month so that's going to be uppercase M two times Dash day day which is going to be lowercase D's dash yyy for the year and then we're going to want our hour minute minutes and then second second so if we actually go ahead and we run that again here we will see that we actually now have the time as well we can see that it is November 1st it's 8 56 32 um at night here um so we don't have the PM or anything like that but we do have that full time so we can actually keep track if you wanted to keep a log of your service status that is how you would do it and if you want the hour in a 24 hour time format I believe all you would have to do is actually change this to a capital h and if we run that again and we go back into test export we have 20 57.07 so the capital H will give you the time in the 24 hour formats whereas the um the lowercase H will give you the time in just the 12 hour format and the capital H will give you the time in the 24 hour formats and once again if we just actually give this a try here there we go it was probably just going off the screen here for the other time where we tried to just do name display name status start type and that was it but once I add the time here it actually does format it nicely into a table so here we can see all of our services as well so we could display it to the console but we can even do one better and send it over to our CSV file I'm getting this error for a service purely because I'm not running this as administrator and that service requires an administrator to get access to so that's why there's a little bit of red on the screen right now but that is how we would export CSV now exporting csvs are very useful as I said a little bit earlier uh maybe your boss wants a report of all the different user accounts that have never logged in yet or that have a password older than six months you can go ahead and get all the user data select just the specific properties that they want and then output that to a CSV file and then give that to them and they could then open it up in Excel or whatever tool that they want to use it and then add some filters to it and look at the data very nicely compared to just dumping that out in a text file the CSV file does make that data look very nice and you can even use some of the import Excel modules that we looked at on this channel and make it even nicer maybe add some pivot tables or some graphs so that is pretty much it for CSV files I hope you guys enjoyed this video I think working with CSV files I work with them multiple times a week um so I think that this is very very useful in the next video we're going to be taking a look at Json files and Json in general which is also going to be very very useful for Powershell because when you're interacting with apis it's always going to be in the Json format usually and when we're working with elastic in the next couple videos so we're going to be starting our elastic videos in a couple weeks we're going to be setting up our server and we are then going to be interacting with it all proof Powershell so I'm going to be showing you guys that and that has a lot of Json in it so that next video with Json will be very very useful to know for that project so if you guys have any questions or comments please let me know in the comments section down below I will try my best to answer you guys directly or if it's something that'll benefit the majority I will try to make a video on it as well also please hit that subscribe button hit that like button also make sure to hit that notification Bell to be notified when that next video comes out and I will see you guys on the next video

Channel: JackedProgrammer

Views: 2,442

Keywords: powershell 7, windows powershell, windows 10, visual studio code powershell, programming, coding, scripting, powershell, powershell for beginners, how to start with powershell, scripts, powershell 7.2, powershell 7 windows 11, windows 11, how to, methods in powershell, powershell scripting, functions in powershell, cmdlets in powershell, powershell tutorial, csv, csv files, import-csv, export-csv, powershell csv, csv data

Id: C7d-GztPFhs

Length: 17min 10sec (1030 seconds)

Published: Mon Nov 28 2022