Automate Excel with ChatGPT (and Python)

in this video I'm going to show you how to use child DBT to generate python code that automates Microsoft Excel we're going to automate tasks such as joining multiple Excel files applying formulas to multiple files and more so let's get started okay for this demo I'm going to use this sales data that I have here these are sales data that are in Excel and we have sales data from January until December so there are 12 Excel files here and the first thing we're gonna automate is joining these Excel files so we want to put all the information from January until December in one single Excel file and you can do this manually or you can make chat GPT to generate python code to do this for you so let's do it this way so first I'm going to show you the content inside one of these files so I'm gonna open this one and this is the preview and basically this file has only sales data uh in one file there are 1000 rows and if we join all of these files we should have 12 000 rows so let's make chat DBT create a script that does all of this for us and here's the prompt it says I have 12 Excel files named and then I write the name of the Excel files so sales January sales February but instead of writing all of them from January until December I just write these three dots and write that last name so sales underscore d e c that stands for December I'm gonna make chat DPT figure out the name of the files let's see if it can do it well and finally I say use Python to concatenate the data inside the 12 Excel files now I press enter and now chat GPT should create an uh script that puts all the Excel files in one single file so let's see how it does this so here's creating a list with the name of the amount then is concatenating the strings with the name sales underscore and well apparently everything is working fine and at the end of the code this DF final is being supported to Excel and this is the name of the Excel file so this looks good this looks really good so now I'm just gonna copy the code and I'm gonna paste it here so we're using pandas for this then we're creating lists and then we're exporting the final data frame that has all the Excel files concatenated so here is the code and now before I run this if you want to follow me you have to keep in mind that your python script should be located in the same directory where the Excel files are located so here I have my python script and here are all the Excel files so now I only have to run this code and see what happens and here I got an error and it says that the file is not on this directory and well apparently chat DBT didn't generate the code that includes also the extension so here we should have something like this that xlsx so with this we have the whole name of the file so for example here sales underscore January which is right here and we need this dot xlsx so here I'm doing this manually but you can also tell Chad gbt to fix its mistake for example here it added the extension xlsx but here just for the sake of this video I'm doing this manually so I'm adding this and now we should have this file concatenated underscore data generated so as you might remember this is the name of the of the data frame that was exported and well it's right here so now I'm going to open this file and see if the 12 000 rows are in this file so here's the file and here is the header now I'm going to the last row and as you can see the last row is twelve thousand and one so we successfully concatenated the data inside all of these Excel files and we did this without writing code but letting cat DPT generate all the python code for us alright for the next automation we're going to apply formulas to multiple Excel files so we're going to work with this sales data again and let's say we want to apply two formulas the sum formula and also the average formula and here we're going to work with two columns the column eight and the column J and for the column H we're going to sum the values from H2 to h1001 and the result is going to be in the cell h1002 and we're going to do the same for the column J but in this case we're going to calculate the average so applying these formulas might look simple when it's only one file but when you have to do this to multiple files for example these files that you can see here it's very time consuming so we're gonna make chat DBT generate python code that applies these two formulas to all the files that you can see here so let's do this so I go to chat GPT and then I write the following prompt so here I say I have multiple Excel files in my directory use Python to apply the sound formula from J2 to j1001 and then the average formula from H2 to h1001 write the results in the cell j1002 and h10002 respectively so with this we're gonna create a script that does all of this not only for one Excel file but for every Excel file that is in my directory so now I press enter and let's see the code that chargpt is going to generate okay chat GPT generated the code but this code doesn't look good because here is not considering all the files that I have in my directory so I'm going to make chat dpz to use an alternative and I'm going to tell it uh use the glove module okay I told chargpt to do it without with the glove module but it didn't understand me so I tried again and apparently now it's generating the code I want it so sometimes it doesn't understand what you want to do or it doesn't do a good job so you have to try again until you find that desired result so here this looks uh good apparently and what I'm gonna do is copy the code to test this out okay I pasted the code in Python I'm going to run this and here apparently the code is not working so I'm gonna ask chat GPT to fix its mistakes and I'll be back in a second okay tell DPT fix the errors and here's the code that it generated so now I'm gonna run this and hopefully we're gonna get the results that we were expecting so now these are finished with success now I'm going back here to my folder and now let's see if the value is there okay I opened one of the files in my folder and now in column H and in column J we should see that formula so now I scroll down and here in h1002 we see that there is the average formula and in J 1002 there is that sound formula so we can see that the code is working so chat DBT generated the code that added the formulas to every single file that is here so you don't have to manually uh write the formulas in every single file that is here but you can let tab DPT generate python code that adds the formulas to all the files for you alright now what if you want to change all the names of the Excel files inside this folder right now as you can see the name of the files is something like this sales underscore and then the name of the month so we have from January until December but what if you want to add the year 2022 in front of the name of the files so something like this 2022 underscore and then the current name so you can do this man finally one by one for these 12 Excel files but if you will have something like 100 files this will be too tedious so we can make cat GPT generate a script that changes the name of all the files with one click so now let's do this here we can write the following prompt use Python to rename all the files in my directory by adding the number 2022 in front so I'm going to press enter and see the code that chargpt is gonna generate first it's filtering only those files that have the xlsx extension and then is adding that 2022 with the underscore in front of the name of the file so here the code is very simple I'm going to copy it and I'm gonna paste it here so it's very simple and I'm going to open this left panel to show you how the names are going to change after I run this script so so right now the name of one of the file for example is sales underscore APR and if I run this now the name is 2022 underscore and sales underscore APR so as you can see all the names were changed and now we have the 2022 underscore at the beginning of each name all right in the last automation consists in editing every single Row in an Excel file for example here I have an Excel file with only phone numbers and what if we want to add the country code to each row in this file so for example now we only have the numbers but we don't have the country code so if we want to add the country code what we have to do well in Excel is use the right or actually that left in this case the left function ending applied in all the columns that might look very simple but when it comes to doing this for multiple files for example here I have two files but but you can have thousands of files or hundreds of files This is Not Practical so what we can do is generate code that edits every single Row in an Excel file and actually not only one Excel file but in all the Excel files inside a folder so let's do this in this case I'm going to add the country code plus 1 to each phone number that is listed in this Excel file so here's the prompt that I'm going to create the prompt test I have multiple CSV files actually I have CSV files in this case this is not a xlsx but it's a CSV file but anyway it's the same and then I have multiple CSV files with only phone numbers in my directory the phone numbers are in a column name font so here there is the the hello phone in this case the header is going to be the name of the column in a data frame that's why I'm specifying this and then I finish this by saying use Python to add the country code Plus 1 to which phone number in the CSV files and that's pretty much it alright GPT finished generating the code and what it did was explain the code into part the first part is the function add country code and the second part is a for Loop uh it's a for Loop where we can apply this function to all the CSV files listed so what I'm going to do is first copy this code and then go to uh here to Python and I'm going to open this folder and this empty script so here I'm gonna paste the code and then I'm going to paste the other code that is here so as I told you before there are two parts and now I'm gonna organize this much better so the library should be on top and with this everything should be ready so here I want to show you that this python script is inside this folder where there are my two CSV files so you have to keep that in mind in case you want to follow my steps so here now I'm going to run this and hopefully this is going to add that plus one country code to all the full numbers so apparently everything was uh was successful and now let's open the CSV files actually I'm just going to open the preview and as you can see here we have the phone number but also we have the country code so plus one to every single row so it's not only for this file but also for this second CSV file so here is the country code and the phone number so with this we successfully created a script that adds the country code to each row in our CSV files and we did this without writing any code but letting chat GPT generate all the python code for us hey thanks for watching this video don't forget to give it a like And subscribe to this channel for more content like this that's it for this video I'll see you on the next one

Channel: The PyCoach

Views: 238,349

Keywords:

Id: A4gYAKry7P0

Length: 13min 33sec (813 seconds)

Published: Fri Dec 23 2022