PowerShell Tutorials : Data Tables

hi and welcome to this powershell tutorial on data tables now we've actually seen data tables already in our gui of videos uh we've actually used them to load data grids but we've actually never taken a look at data tables uh separately to actually see how efficient they are at storing data and filtering data so we're actually taking a look an example afterwards of just importing a csv and then using the powershell methods of piping with a where clause to filter the data and actually see where the data tables would actually be very beneficial to use so i've created this data uh the csv data with a header of just sequence first name last name and age now this is all randomly generated data i have 10 000 lines so i figured that that would be a big enough that to actually really show uh the beneficial uses of data tables of course with smaller data sets uh the differences get very very minimal and at that point really doesn't matter but definitely if you extrapolate this to millions of rows um it definitely gets significantly better so let's go ahead and let's actually get started by first creating our data table object so let's create a variable called data table and we're going to make that equal to new object and that's going to be an object of system.data dot data table and then what we need to do is we need to load in that csv data and actually create our columns and then load in the data for it so whereas compared to if we were using an array list or just importing the csv data with import csv with an arraylist we just create our arraylist we load in the data we create the objects with the properties that we want and then we store those objects into the arraylist what a data table is we actually need to set the columns first and then what we do afterwards is we insert the rows um with the uh data ordered exactly as we ordered the rows um in our the exact way that we ordered our columns so let's first go ahead and let's actually import this data dash uh data.csv so we're going to create a variable called csv data and we're going to make that equal to get content and we're going to do a path here and we're just going to grab the path of our data here do shift copy path and let's paste that in here and then what we're going to want to do is we're going to want to create our columns variable and now our columns variable is actually going to be equal to csv data square bracket 0 so that's actually going to select the first line in our csv file which we know is going to be the header and we are going to actually split that on a comma because we know that it is a comma delimited csv file so if we actually run all of this code here and we look at our columns variable we will actually see that we do get an array of columns and then the only other thing that we need to do now is actually remove that first line from the data so when we actually iterate through it we don't insert the column row as a row of data so let's actually go ahead and reassign our csv data variable to csv data and we're going to pipe that to select and then space dash skip one so that will actually pipe the csv data we're going to select everything that's in there and we're going to skip the first line and then what we need to do now is we need to associate or assign those columns to our data table so to do that we're going to do data table dot columns and we're going to do a dot add and here we're going to do it an add range so you can do an add if you want to add an individual column at a time or you can do add range and add a array of column names so we're going to pass in our columns and then what we're going to want to do is we're going to want to do a for each loop to iterate through our rows in our file i'm going to do a for each row in csv data open and close curly bracket i'm going to put a void here because what happens when you add a row into a data table you actually get the data that you're adding in as an output i don't really want to display that to the screen that makes a lot to look at at the end so we're going to do a data table dot rows dot add and once again here we're going to add our row but we also need to do a dot split once again on the comma just to separate those values out and then let's go ahead and let's do data table and let's just do select uh first one just to see with the top value here so as you can see that goes pretty fast to load in the 10 000 lines of data as we can see here we get our column names and we have our first line of data which we actually see here it is correct so we know that it worked okay so now to actually filter this data there is actually a very cool way to do it with data tables but that actually requires a new object called a data view so what i'm going to actually filter on is i want everybody with whose age is 30 or over so let's actually create a variable here i'm actually going to call it age filter because what i'm going to be doing later on so age filter we are going to go ahead and create that to a new object and we're going to make that object of typesystem.data.dataview and we're going to load up that view with data table so that's going to take all the data from data table and put it into this data view object and then what we're going to go ahead and do is we're going to go and do a age filter here and dot row filter and what we can do here we're going to make that equal to a set of double quotes and here we're going to put a conditional statement that probably most people are used to very similar to like a sequel statement so we're just going to do age greater than or equal to 30 and let's just go ahead and let's see what that h filter is all right so there is everybody over the age of 30. uh so let's actually go ahead and let's do another one here so i'm just going to copy those two lines and let's do a lesser than 30 and let's see what that looks like so here we can actually see that it doesn't actually change what is actually in in age filter so this data table will always have all the data and then when we apply a filter to it it just changes what data is actually visible so as you can see here we select everything that's over 30 but then everything that's under 30 and we actually do get the correct data back so that's actually awesome for this uh this is where the benefits really really kind of show is when we start doing multiple filters um on a certain data set so let's actually go ahead and let's see how fast we can do these two filters and actually do the entire creation of the object and loading the data in so to do that here we've actually seen this before so we can actually measure the efficiency here which is measure command and then expression we're going to do a curly bracket and let's do the closing curly bracket all the way at the bottom here so if we run this we actually see that it runs the entire line of code in 252 milliseconds now of course uh that will vary um depending on how busy your computer is at doing other things and also how much ram your computer has uh the cpu and a bunch of different factors but here we can see here that we're running it and we're getting kind of like in the 170 to 230 range sometimes 260 which isn't that bad uh so let's go ahead and let's actually compare this to just a simple import csv and doing a pipe where clause uh so let's go ahead and let's create our variable here of list data and we're going to store that to import csv and we are going to make our path here and we're just going to copy that same path so we're dealing with the exact same data and we're going to do the delimiter which is a comma here and then what we're going to do is we're going to do a list data and we're going to pipe that to where age is greater than or equal to birdie and let's do a list data and we're going to pipe that to list data and we're going to pipe that to where age is less than 30. all right and let's go ahead and let's measure the command here and let's do an expression open curly brackets and close the curly bracket over here and if we run this here oops so already we can see that we get a very very high value now of course like i said before uh this will kind of vary so we see here 335 we run it again we get 246 237 294 3 234 226 271 245 231 294 237 so as you can see we're always in the kind of mid to 200s to low 300s in milliseconds but with the data table we actually still get mid uh 100s and then the high points are about the mid 200s uh so it's highest point with the data table is actually the lowest point which is the simple import csv now of course looking at both of these um the import csv is definitely a lot easier to write um but if you're dealing with a lot of data like this would definitely have to be a significant amount of data that you're going to be dealing with and actually would really depend on how many times you're going to be filtering that data it's really the filtering that makes that really really big difference the data table will be able to filter data a lot faster than this pipe object so that's really going to where it comes in if we kept adding different filters here so if we added another one here and we said uh we want where the age is greater than or equal to 65 to find the people that are retired and let's once again do that down here and do 65 so if we run the data table version we will see that we get 224 202 245 178. so the times really don't change in the data table but as soon as we go into that import csv and where we're piping we're going to see now that we get 367 365 316 309 367 312 so now we're constantly in the 300s so the more times that you're going to be filtering data definitely i would recommend you going with the data table and just the larger the data set i would definitely recommend data tables as well instead of just the simple import csv you're going to spend a little bit more time typing out your code but if you're dealing with hundreds and thousands or even millions of lines of data the data table will definitely show great efficiency if powershell is your language of choice for dealing with that type of data so that is pretty much it with uh data tables for this video if you guys have any comments or questions please let me know down below in the comment section if you guys have a specific question for something in powershell let me know or anything else programming or system related uh let me know if it's something that i know the answer to and that can benefit a lot of people i will make a video on it uh if it's a little bit more specific i'll just answer you directly and also be sure to subscribe and hit that like button and also hit that notification bell to be notified when the next video comes out i will see you guys on the next video

Channel: JackedProgrammer

Views: 7,822

Keywords: powershell basics, powershell, windows powershell, programming, coding, scripting, powershell scripting, powershell scripting tutorial, powershell tutorial, data grid, data grid powershell, how to, powershell api responses, data sets powershell, data sets, data, data table, data table powershell, data tables powershell, faster data filtering powershell, powershell automation, powershell beginners, using data tables in powershell, data table tutorial

Id: QMXoNSi04wg

Length: 13min 43sec (823 seconds)

Published: Mon Jun 27 2022