Automate EXCEL with PYTHON |Loop|Vlookup|Read & Write Excel#python#openpyxl#selenium

hi viewers welcome to my YouTube channel cornerstring my name is Vikram and in this video I am going to talk about one of the popular python library that is called as the Open PI Excel I have created this video for the people who are new to Python and would really love to understand that how python interacts with the Excel right so first thing remains first what is Open PI Excel Open PI Excel is one of the Python Library which we use to read and write Excel file also it allows users to access cells rows and columns in an Excel worksheet to perform basic Excel operations so I'm setting up the stage for the topics which we are going to cover in this video I will be talking about how to read data from Excel file using openpixel how to write data in Excel file using Open PI Excel will use the loop to read and print to write and print and recover both the approaches the static and the dynamic approach as well and in the latest stage of the video I will cover one very simple formula some function and we'll end this video with one famous formula known as vlooker so without further Ado let me quickly jump on to the buy charm where we will start writing up the code [Music] foreign [Music] start with creating the python file I'm naming it as demo and then import open by Excel in case it's not installing your system how you can do that you click on the settings icon here in the python interpreter you will be able to see all the list of the packages or the library that has been installed in your system click on the plus icon type your library name so in my scenario it's giving me the name of the library with the blue phone which means it's already installed and the beauty is there on the right hand side in the description box you will be able to see the description as well of that particular library right so I'm closing this one the second way to perform the same you can go to your programs and then see where your python app is installed click on the open file location type your CMD this will open the command prompt use the PIP command install open by Excel and hit the enter button this will take like 30 to 40 seconds to install it in your system and we are done so step one will be to save the path where the file resides okay I'm creating one variable but so my file is stored in the python folder in the desktop location and the name of the file is my data copying the page copying the path and storing it here step two open or load this workbook I'm creating another variable WP using the Open PI Excel and then loading this workbook which is stored in the path string three save the fade names [Music] workbook there are three sheet tabs read write and employ so that's the reason I'm going to create three variables sh1 sh2 and sh3 where I will be storing the sheet names read remember that python is case sensitive so this should match exact whatever the name you are mentioning here [Music] WB and then employ so we are done let's print sh1 sh2 ss3 so before running the code I'm just giving you a spoiler that we will get two errors one will be the Unicode Escape error and second one will be the permission denied let's run this and we'll resolve it after okay or we'll talk about why why we are going to get that get those errors see as expected Unicode Escape is the error to resolve this error we need to add the raw string in front of the path string here a string literal which will make python understand that the the backslashes which we are going to use in this path these are the actual backslashes and not a part of the escape code okay let's run now we will be getting the permission denied error see why we are getting this error because the Excel file which we are going to use is already opened here right we need to ensure that this should be get close okay now we will be able to see the results see worksheet read worksheet write and worksheet employee so we are going to tell here [Music] so let's cover the first portion the static approach how to read from Excel file okay the static approach it says that okay within the WB there's a sheet one with the name read and there are rows there are cells there are columns right so we have to make we need to make python understand that what we need to read okay so how we can do that I'm creating one variable let's say with the first value and another one with this second value okay so in this sheets sh1 dot cell then your row number is equal to 2 and your column number is equal to one whatever the value is there they would like to pass it to First value again sh1 dots cell rho is equal to 2 and then your column is equal to let's say 2. whatever the value please pass it to Second value and then print first value print second value okay so the results should match with the Amazon and theater us okay close this Excel workbook and hit the Run button perfect see the first value it's giving us the company name and the co till you is this is the headquarter location for Amazon so till here we are good okay now [Music] let's talk about the second approach the dynamic approach same how to read using Loop simple so in the dynamic approach the first thing is to make python understand that in the first read the name of the sheet is read how many rows are there how many columns are there what is the maximum number of rows what are the maximum number of the columns so how we can achieve let's create two variable here row count and then your call called columns count okay sh1 dot Max row sh1 dot Max column right so we should be able to get uh the maximum number of the rows and the maximum of the column in the sheet1 let's print it print row count then your uh column count okay hit the Run button see we are getting six rows as the as the maximum rows and two columns is this true let's see yeah so you can see that six is the count for the rows and two are the columns so we are good great great okay for the people uh who have done the coding in the VBA in the Excel VBA part for them it's going to be very easy because we are not we are not doing something new right in VBA we do the coding like this Shades and then industry the name of the sheetab is read and then go to this range and then whatever the value is there please pass it to let's say test right we have used the same here but with the some changes you know in the sh1.0 is equal to column is equal to one in this way otherwise everything is same so uh now we have the maximum row count we have the maximum column count as well we can move towards the loop part so for C in range 1 2 column count plus one another loop for are in range 1 2 row count plus one [Music] so you might be wondering that why I have added plus 1 for the column count and the row count right the reason is that python by default it starts to loop from zero and increment it by one and stops before the specified number so for an instance the row count is six so the loop will start from 1 and it will end at five as per the definition so we are increasing that five by one count so that it covers the last row as well I hope it's clear so again here sh1 dot cell then your row is equal to your R and the column is equal to C Dot value whatever this value uh let's create another variable [Music] test one and then you can print the sorry test one let's run the code and see if it's working or not yeah perfect so company name Amazon Tesla Microsoft Oracle Adobe and so and so forth with the headquarter location and the last one is the California us so we're good till here uh but this is something for example let's say you would like to see these all results in one line of the code how you can achieve right so create one variable my list and list so what is list list is a sequential data type which stores the collection of the data okay which stores the ordered collection of the data so we're going to use the my list here uh how we can do that so we are going to introduce here my list dot append and then whatever the value it's in the test one please append it and then make it in the sequential form and you can go and type your print or rather than printing it here um let's do it in this way print my list see now we are getting all the results in in a list form company name Amazon Tesla Microsoft Oracle and so and so forth right so this is one of the way that how to read the data using the loop right so that's a dynamic approach let's talk about another approach there's another one let's cover that as well in this way we'll use the range row and cell concept so for rule in range sh1 uh A1 to B6 for cell in row print cell Dot value time to run the code we're getting error permission denied remember we need to close the Excel workbook let's run it again yes see the head coach the location Amazon company name everything is getting printed so this is another one of the way to read the cell information okay now we will move towards the another section of this video how to write in Excel one very simple way sh1 and suppose uh in sh1 uh maybe in F 5 we would like to pass the value 6 times 6. [Music] and then wbe dot save path perform the validation in the sh1 yeah F5 6 times 6. that's correct congratulations all right so now let's work one of the problem State man so for example I would like to print if row is odd then print odd else if row number as E1 then print then print P1 okay and perform this activity till my 50th till my 50 cell okay 450 cells okay it's closing this one so remember we need to find that how many rows are being used in sheet 2. so creating an a again one variable row counts is equal to assets two dot Max hello then print row dot counts and see you are getting 50 so maximum rows are 50. for row N sh2 first of all we are using the static approach here so for Row in SS2 A1 to a 50 [Music] for cell in row if cell dot row whatever is the row number if it's divisible by 2 and the remainder is equal to 0 then in that scenario we would like to print cell dot value is equal to A1 else we want cell dot value is equal to odd and again we are saving the information WP dot save but time to perform the quality check so see we are getting successfully the odd and even as per the row number till 50th cell but this uh this is static approach now I would like to cover the dynamic approach here so again let's say uh now this time this time my row number gets increased by 20 and 70th is the last cell a70 is the range reference okay because Excel it works as per the A1 style referencing so closing this one now what I'm going to do instead of this A1 to A50 which is the dynamic one I would like to make it a dynamic one sorry instead of the static we would like to pass the dynamic variable here so that whatever the rows if it's going to be increased or decrease it accommodate accordingly how we can do that a very simple one uh let's create one variable such as dummy and dummy is equal to uh A1 from A1 my range will get start and concatenate it with Str or A1 Plus [Music] this one then Plus uh again a and then Plus passing the row counts here so whatever the row counts it should be passed to the a now we can directly pass this dummy here so for Row in SS2 and dummy and then for sale in row everything is going to rain remain same let me print the dummy as well so that you understand why I've created this one and click on the Run button okay so why I've created the dummy variable here with A1 then concatenate and then concatenate and then a then stro counts because you know that Excel it works in the A1 style referencing right so so my range is from A1 up to the last cell in that particular range so so we know that our Excel it holds the information in SS2 with the name as Write Sheet name and from A1 to a70 we have to we need to use the range right so 70th is my last use row okay so it may get reduce it may get increased we we do not know right so we would like this to be dynamic one this is the needs of the user so as Excel it use the A1 style referencing so we need to pass this kind of the range A1 then a70 right A1 to a70 so we have used the concatenate to store this text and then we have you know pass this text to the SS2 in the same way as we have done before okay so there's no change except we have created in in the more Dynamic way let's see whether the answer is coming over there or not so now this time the odd event should get print till 17th yeah see congratulations this worked all right so as I promised we will be covering to uh functions here one is the sum and then we will in this video with vlookup I Know video is going lengthy but trust me this is something which is very very important from The Beginner's perspective right in order to perform any kind of the automation you need to know the basic fundamentals right so please pay close attention to these two functions we are going to cover okay some function will sum the two or more values okay so here if you would like to perform this sum function in Excel we can do simple sum and then your range of numbers right in Excel in Python also we can use the same here some of this so ss3 then in F3 we would like to do the sum so I'm just copying and pasting the same formula we have used in the Excel worksheet and then WP Dot save path remember we need to close the workbook closing it down and then clicking on the Run button perform validation see number is printed here with this sum formula C2 to c61 so that's all about the sum uh let's work on the vlookup the one of my favorite formula in Excel vlookup so this is the table with totals 61 rows and three columns which holds the information of the employee ID and their department and and their salary per month right so we would like to perform the vlookup in order to find the department name of these particular seven employees so if I need to perform this operation in Excel I can directly use vlookup and your lookup value then your table array making it a fixed one so that range remains constant and then from the second column and we would like the exact match this is how we perform the vlookup in Excel so I'm not doing anything new I'm just copying and pasting this information this particular in Python to be further use okay so here my range I want my results to be get print in G11 to g17 so again we can use the same like for Row in range or for Row in range ss3 then your column name is G from G11 to g17 you need the result right so G11 to g17 a row in ss3 G11 to g17 what do you want for cell in row then your cell dot value is equal to your this formula which I have pasted okay and we are good then you are saving this information WB dot save path for Row in ss3 for cell in row cell dot value is equal to vlookup and we are good let's run remember we need to close this workbook okay we successfully uh executed the code but something is wrong here because we are getting the department for all these employee ideas Administration as let's check for one two three nine eight there should be quality but it's giving us the administration so there's something which is wrong right so we have used the f11 as a fixed one right this is not getting changed here again FL f11 so this should be F12 in order to get the right result and here f13 and so and so on right so how to fix this one [Music] closing this one you can use the curly braces here and this should be able to fix your problem okay so why we have used the curly braces here you remember that for sale in row there is this row number right so for each cell if you would like to see what is the row number you can save this information in just a check variable and you can check it as well print check right sorry print check so whatever the row is passing to the check this will come to your F calipresses here right so now this will move incrementally starting from f11 then F12 and so and so forth till the end f17 Okay so let's run the code again see first of all it's passing the 11 to the F calibrases then F12 then f13 so it's a it's a list of the array right so 11 12 13 14 15 16 17 these are the particular uh row particular cell references which will get used in the F so f11 till F 17 and then we'll use the vlookup over there let's perform the validation here all right this is already opened up oh there's something wrong here okay so uh we can fix this error by applying the format and then cell dot row hopefully they should resolve the problem let's open the my data again now this time results are appearing correctly so for one two nine three eight that was quality yes correct so this is how we can perform the vlookup using the Open PI Excel I hope you have found this video useful and informative please do like share and subscribe to my YouTube channel for all upcoming videos notification thanks for watching I wish you all the best

Channel: Cornerstring

Views: 5,422

Keywords:

Id: hY7Gv-MnANE

Length: 27min 27sec (1647 seconds)

Published: Thu Jul 20 2023