PowerShell Tutorials : Editing CSV files (Adding columns, editing data)

hi and welcome to this video in this video we're going to be looking at editing csv files in powershell uh like adding a column or editing data in a cell of a csv file this was actually a video request so let's go ahead and let's get started on this here so i actually already have a csv file generated that i've used the convert csv website for so here it is uh it's just called names and if i open this here in notepad we will see uh that just has like a sequential number first and a last name so what we're going to be doing is we're just going to be going through the first and last name and we're going to be adding a column the first column we're going to be adding is going to be a computed columns so basically we're going to be taking something from the other columns and computing them together to make another column and then we're actually just going to be adding another column with just like a true value so it's going to be like a done column and we're just going to set the done to true we can also implement logic to set that to false but it's just to give you two different examples of how to add columns to a csv file and then we're also going to be looking at how to change values in the csv file as well so let's go ahead and let's get started here so i already have my powershell window open and let me just grab the path to this file here so what i like to do kind of what we did in the selenium files which i didn't cover in the actual tutorials we just kind of referenced the path directly either way really works this just makes it easier because we're going to be outputting to the same folder location as the input file so what i like to do is i just like to do a script root and we're just going to put that in there and then we're going to put input data which is going to be our input file path which is actually then going to be script root names dot csv and then we're gonna have a output path as well uh so let's do output file path and we're gonna set this to script root and we're going to do names dash output dot csv all right so we have our script root file which references our folder which this file is in and then we specify the actual file and then we specify a output file path because we're not going to want to overwrite our actual input data um it's definitely best to always keep your data safe from any modifications this way if anything goes arise you still have the original data all right and now what we're going to want to do is we're going to want to do a for each here actually first we're going to want to import the data so let's do names equals import csv and then we're going to specify a path and we're going to do that as our input file path our delimiter is a comma in this case so i didn't really have to put this but this just adds a little bit more if someone reads it they know exactly what the delimiters expected and they can change that and let's do our encoding as default here now let's do a for each name in names here and we are just going to go ahead and we are just going to put name here for now and we're just going to make sure that this works so let's go ahead and let's run this script here so here we have all the data with all the names and the sequence number here so that is perfect so what we want to do first is we want to add a column called full name so we're going to be adding the first and the last name and the way that we're going to be doing is we're going to do it last name comma first name so let's go ahead and let's create a variable called full name equals now this you've already seen this in my other videos let's just do last and then we're going to do a comma and then we're going to put name dot first now we don't actually need to store this in a variable we could do it all in one line in the next line that i go over but this way it's just easier to kind of see what's going on it's a lot easier to read and then what we're going to want to do is we're going to want to do a add member and we're going to want to put the input object as name and then we're going to put member type as a note property and then for the name we are going to want to put that as full name and the value we are going to put that as full name here now what we can do is if we look at names and then we do export csv path and then we put in our file path our output file path our delimiter which is going to be a comma and then a node type information and let's run this whole script here and let's see what our file looks like so we have our output file here and if we go to open with and we go into notepad we actually do see that we actually do have the column of full name and we can actually see our full name here which is vasquez virginia so probably the comma wasn't really a very good idea but it's inside the quotes so it shouldn't be a problem but what we could do in this case is let's take out the comma let's put the first name in front of the last name a little bit more traditional there and uh let's just close this and let's run this again here and let's look at it now it should be a little bit easier there you go so we have first last virginia vasquez and then full name virginia vasquez as well all these names are false they were just generated from a computer here so if your name is on here it's purely coincidental so that's how you would add a column computed based on the last two columns now let's say you had a you wanted to do a operation so what you could do is you could do some api or database call and then based on the result of that what you would do is you would have like an if statement we're not going to do an if statement because we're not doing an api or database call or anything like that but then what you would do is you would do add member input object and the reason why this works is because we have our import csv here into names what this does is it creates a array of all the different um all the different objects each line in a csv then becomes a object inside of this array and then what we're doing with the 4-h is we're grabbing each object in that array and we're treating it as its own object so what we do here is we're adding a member and we're putting in the object that we want which is the current row and what we're doing is we're adding a property of full name so we're actually adding another column because each column is a property so that's kind of how this works now you could do it in a way of you're doing a for each you grab all the data from the csv file and before this you would create like an array list up here so you would create oops give me one second here i think i duplicated some stuff here so give me two there you go so here you would do create array list and then what you would do is you would loop through all the csv data and you would rip that csv data and create another object and then add that object into the arraylist that becomes a little bit less efficient if all you're doing is simply adding a column which was the request was really just to know how to add a column um so i wouldn't recommend it that way if you're just simply adding a column i would just do it this way but know that there are other ways of doing it especially if you want a little bit more logic behind it or depending on what you want to do with the data but most of the time this method will will be sufficient and actually will be much more efficient than creating a new array list and creating a whole bunch of new objects and adding those objects to the arraylist because that becomes a lot of operations so we're going to be adding a member here and we're going to put name and we're going to say done and we're going to give it a value of true here and that should be it so if we go ahead and we run this hopefully i had the file open it should still have overwritten it but let's see yes so here we have now we have our five rows we have the full name and we have the done row and we have all of our values as true so what we're going to do is we actually want to change now this would be more so if you wanted to change like every value in a row this isn't going to be super super useful um for your everyday scripts um but i wanted to show this just so you guys know that it is possible now let's say we wanted to change everybody's last name to smith what you would do is you would do name dot last equals smith and this will actually change it so if we run this here we will actually see in the output file that everybody's name is now smith and even it affected the full name because i did it before the full name value was calculated if you did it after their last names would still be using their original last names so if we actually just change the position of this to here so just to show you guys once more what it looks like before i run it so here we have everybody's last name as smith and in their full name it also references smith as well so let's go ahead and with the code change here of changing the last name afterwards if we run this here and we go into open with and we go into powershell all right so let me just zoom in here so here we have all the last names have changed to smith but their full names still have their original last names so the order of operations in this case really does matter so make sure that if you do any edits and if you do something like a pre-calculated value if we had done this all in one line of the ad member we wouldn't have this potential problem but in our case because we have this calculated value if we want the last name here to be the new last name we have to make sure to always do that before and that is pretty much how you add uh columns to a csv file through powershell and edit uh entire columns through powershell now what you could do if you knew that specifically like you had a bunch of last names that needed to be changed uh you could add an if statement that will only change specific names if you know the id number you could do an if dollar sign name dot sequence is equal to five and then change the name dot last on that specific one it gives you a lot of flexibility here but this is the base script for how you would modify csv files i hope you guys enjoyed this video if you guys have any other requests definitely feel free to comment down below and i will always look at them if i know how to do it i'll do it right away if i have to learn something i will go ahead and read and learn something and try to show you guys to the best of my ability but please like subscribe and hit that notification bell so you're notified when that next video comes out and i will see you on the next video

Channel: JackedProgrammer

Views: 19,475

Keywords: powershell, csv, automation, scripting, programming, coding

Id: Xk-Ot2xLw9M

Length: 14min 36sec (876 seconds)

Published: Thu Sep 09 2021