PowerShell Tutorial: Import & Export CSV Data 🔥⌨️ DevOps | Automation | Developer

hey guys my name is Trevor Sullivan and welcome back to my video channel thanks so much for joining me for another video and what we're going to be covering in this particular video is how you can Import and Export CSV or comma separated value data from a Powershell automation script now I've already got Powershell installed in front of me here if I just do PS version table you can see I'm running the latest version of Powershell core Edition here on the Windows operating system I'm also using the windows terminal here and we're just going to be taking a look at a couple of different commands keep in mind that you can install Powershell on Linux and Mac OS as well natively and you can also run a Powershell inside of a Docker container so it's really easy to get Powershell spun up even if you don't want to install it necessarily on your system using containers right so we're going to be looking at the export CSV command here along with its sister command which is known as import CSV and these are really nice functions because these are actually built directly into Powershell these are not third-party modules that you have to install after the fact so as long as you've got Powershell running you should automatically have access to these particular commands and they're really essential to working with any kind of CSV data now how do we actually get some CSV data well I went out on the internet and did a quick search and found some publicly accessible data on data.gov over here this is actually a really interesting data set right here that I found that's been pretty recently updated and what it contains is electric vehicle population data from the Washington State Department of Licensing so this wouldn't be National Data across the entire United States this would be only scoped to Washington State because I guess they have some kind of law that allows them to distribute just the Department of Licensing data for battery operated vehicles or plug-in hybrids so this is a really interesting data set you can download it using a different series of formats right here but one of the formats that it's provided in is comma separated values so of course since we're learning about CSV that's the format that we'll download it in so feel free to grab that same data set and play around with it one of the things that's nice about it is that it actually has a pretty substantial amount of data and it's logically organized into a bunch of different fields that are pretty easily recognizable so for example right here we've got things like which county in Washington state the vehicle is registered in it even gives us the VIN number which is pretty cool we've also got the city that it was registered in the state that it was registered registered in so obviously that's always going to be Washington in this case we also have the postal code we have the year the manufacturing year the model or manufacturer make model and we also have let's see electric vehicle type so that tells us if it's just battery only or if it is a plug-in hybrid here we've got a clean alternative fuel vehicle eligibility not really sure what that means but I don't necessarily care about that particular field we've also got electric range so this is actually really interesting because we can actually see you know how many different vehicles have different ranges based on that field we also have a field here that looks like it may not be filled out or at least not comprehensively called base MSRP manufacturers suggested retail price and if I just kind of look through here I don't see a lot of values filled in so I think that field would have pretty limited usefulness and there's a few other fields here as well but I think the main ones that I'm probably interested in are things like what county is it in what's the VIN number so I can get more information about the vehicle what city is it in so we can kind of look at you know what cities are the most popular for electric vehicles and we could also see like what the make model year and manufacture of the vehicle is so let's go ahead and get this data into our Powershell script so this file is about I think it's about 35 32 megabytes in size something like that depending on if you divide by a thousand or 1024. so what we're going to do for starters is just call import CSV and we have the option to specify a delimiter here so when we open up that file in Excel we can just see all the different fields because Excel kind of automatically parses the file but if we open it up in notepad you can see that a comma is actually the delimiter but in some files even if they're called csvs you might actually notice that they use maybe a semicolon as a delimiter or a pipe character as a delimiter and so in those cases you can actually specify an alternative delimiter character right here in the import CSV command but we're going to ignore that since it automatically uses a comma but then we're going to specify the path so I'll just go into my current directory right here say I want the electric vehicle population data file and then let's see what other parameters there are we do have the ability to specify headers if we want to here as well not all CSV files necessarily have the first row as being a header row so in that case you can actually specify which headers you want to use right there and that's pretty much all we really care about but what we're going to do is basically just say import CSV specify the path and then just create a variable to hold that data and in this case we'll just call it data but you can use whatever variable name that you want to so as you can see even though it's a pretty large file it was actually pretty fast to import that so now if we just do data.count we can see that there are close to 139 000 records inside of this electric vehicle CSV file right so a lot of interesting data here to kind of parse and grep through right so what I'm going to do for starters is just inspect the first couple of Records so we'll index into the objects here and maybe just take a look at the first 10 records here and as you can see it's going to be shown in list form here by default so we're going to see the property names on the left or field names and then on the right hand side we'll see the actual values for each of those fields but we could actually just kind of filter down this data to things that we're interested in so if I wanted to I could say take these first 10 records and pipe them into format table Dash Auto size and then specify the properties that we want so for now I'll just grab let's say County city state and make so County City and make I'm actually going to ignore States because it's always going to be Washington anyway so as you can see in the first few records here we can just kind of sample that data we have a few different counties listed here we have a few different cities a few different makes and a few different models somebody's got the jaguar in there so they must be doing pretty well financially all right so now that we've kind of sampled the data here we can kind of see what we're interested in let's go ahead and explore some more records so we can simply change the index here and say I want the next 10 records so we'll just say 10 to 19 for example and so now we get kind of a different sampling of that data we could also go you know kind of towards the end of it and say I want records 138 000 to 138 000 and 9 and that'll give us another 10 records so that allows us to kind of sort through the different records in this CSV file and find what we're looking for so a lot of cool stuff that you can do with it you can use Powershell commands like select objects to grab certain properties as well you can use commands like group object to group the data based on things like county or city and kind of get counts or maybe even the make and model so you can kind of figure out what the most popular model is what the most popular county is for electric vehicles and things like that so feel free to just kind of sort through this data whatever way that you want to so that's how you can import a CSV file we can also export data from our Powershell scripts out to a CSV file now I've already got some data imported here but I'm going to show you another example where I just manually construct some objects in Powershell and then we're going to use the export CSV command here to Simply take those input objects and Export them to a file on the local file system you can also specify what your delimiter is I think yep there's the delimiter parameter right here so if we wanted to override the comma as the default delimiter then we could just specify that as a character right here so what I'm going to do is actually take a sampling of our data so let's say that I only wanted these four properties for these specific 10 records that I've grabbed right here and then I want to just export that snapshot of this much larger CSV file into a smaller CSV file so basically we're just segmenting the data and limiting the number of fields that we're retrieving so instead of piping those data records into format table I could actually pipe it into select object and specify the properties that I want to select and filter down those fields so once again we're going to choose County City and we'll do make and model and I'm actually going to put City before County right here just because the city is kind of a smaller entity and a county is a much larger entity so I'm going to start with the smallest and then kind of move up to the larger County there so now we've got all the same data it's just sorted a little bit differently here where we have City on the left then County then make and model and so now I can actually take this data now that I've used select object on it to only select certain properties and I can simply pipe all of these custom objects into the export CSV command let's do export CSV Dash path and then we'll just say 1000 or 138 000 Dash ten so I'm basically just saying I'm starting at record number 138 000 and I'm grabbing 10 records and will just append the fields like City County make and model dot CSV so when we look at that file name with get child item we can kind of clearly see which subset it is so where's the starting record how many records did we retrieve and what are the fields that we retrieved in that file so now we've crunched that 3 35 or 32 megabyte file down to just 440 bytes because we omitted tons of fields we omitted many records as well and so we've got a tiny snapshot of that much larger file so now if we just do a notepad on that 138 000 file right here you can see that we've got a CSV file also Powershell decided to put double quotes around each of the values as well so that if there are spaces in the values it just kind of treats it as a singular value so this is kind of what it looks like to take some objects and Export it to CSV now this is just some interesting vehicle data that I found that we can kind of play around with but you can really just take any arbitrary object and actually send that to a CSV file as well so for example let's say that I run get process right here and I've got a bunch of process information about all the different processes that are running on my system and then let's say that I wanted to just maybe get some information about all the Chrome processes like maybe the process ID and the process name and then I can just send that to a CSV file well what we can do is take get process and we'll just pipe it into select object here once again because we want to limit the properties that we're going to select then I'll grab the ID and name but I want to filter that down to only the processes with chrome in the name so back on the left hand side of Select object I'll just say where the let me do where the PS item dot name is equal to Chrome and then we'll type that into select object so now we only have the Chrome processes and we have the ID and the name so now I can take these processes and just spit them out to a file so that in the future I have a snapshot of the Chrome processes that we're currently running on on my system at the time that we took this snapshot so once again we'll just do export CSV Dash path and I'll just say process underscore snapshot.csv and I'm also going to specify a different delimiter here so I'll actually use a pipe character but because a pipe character has a special meaning inside Powershell I'm going to surround the pipe character in a couple of quotes here so that Powershell knows that I'm not trying to pipe into another command all right so now if we do get child item again you can see we've got this process snapshot file and if we just do notepad process snapshot.csv you can see that our CSV file which is technically a pipe separated values file is now using the pipe as its separator but the data is other than that in the same format so it's really useful to get you know any kind of system information or work with vehicle data work with data that you get from some other kind of source there's tons of different sample CSV files that you can get out there that have interesting data or if you just need to exchange data between different systems CSV is one format of many that you could potentially use for that now I personally prefer Json just because Json is ubiquitous across many different programming languages these days but again this is just another option that you have in your bucket in case maybe you're interacting with a vendor that you know gives you a CSV file and doesn't even give you an option of getting a raw Json file that you can import so anyways I just wanted to do a quick video and show you how to work with CSV data from your Powershell scripts thank you for watching and we'll see you in the next video take care

Channel: Trevor Sullivan

Views: 5,803

Keywords:

Id: AVGoGjAuid0

Length: 14min 29sec (869 seconds)

Published: Tue Aug 01 2023