How to Read Excel Data in Python. Openpyxl Tutorial #2

welcome back everyone in this demo we will see how we can access values from the excel file now before i begin let's go back quickly to our excel sheet and let's go to our sheet number one we have two columns and eight rows so we can basically in our python file target any of this shell individually or in a group or whichever way we like and extract this value in our python program i have pre-populated two line i have created my workbook object and selected the seat one so it's ready to go if you have any question regarding selecting workbook or a worksheet please go to my first tutorial and you will see how those two things work let's say i wanted to select this 1400 value now i'm using here libreoffice it's open source excel editor you might be using other software probably microsoft texture or any other open source software out there in any actual editor we will have two important things to understand first all this a b c d e f right this will go all the way till z z these are called columns and this horizontal lines one two three four five all the way until i don't remember exact value and those are called rows any particular location in this excel sheet we can point that out using column row number we can address any of the cell using the column row so for instance there isn't one value in this cell location for this cell will be it's in column d and row number five so location for this cell is d5 let's see any somewhere else here this cell location is column g and row number 11 so the address for this cell is g11 the similar way i wanted to target 1400 particularly so address for this is column b and row 5. i can go back to my python file here and simply target that cell using the w as the worksheet object and in here i can pass the address of my value and again let's print that first now this will not give us what we want and if i run this file it's giving me location of that action file okay but we don't want location we want to extract a value of that cell so to do that we need to pass another command call dot value now if i delete on my python file there we go we can see 1400 here now this 1400 is coming out of this location so here we need to use the worksheet object pass the location of the cell and we have to put dot value to extract the cell now there is another way we can do that so we can use the integer format basically we can count our column and rows as well so this is the first column second column third column and so on similar way this is first row second row and third row and so on another method is to use cell method we will need to pass row and column number uh let's go back to our excel sheet and say i want to extract this robin so robin it's in first column and a sixth row so i can pass it here row number six and column number is equal to 1 and again we need to pass a value if you want to see this in a console we need to add that inside the print statement rerun our program i read on that i can see robin here right this 1400 is coming out of this sixth line and this robin is the extractor successfully using this row in column format now there is another option we can also extract particular range of values so let's say we want to extract first two column and file we want to extract start from john his balance bob his balance and so on till the team in his balance to do that we simply need to pass the cell location using the column so here we want to start at john so john is in a2 location and it needs to end at this b5 pass that in here again the syntax is we have to use worksheet and in square bracket pass the starting cell first cell and the ending cell we can save that in a variable and let's print this value range wow if you can see in here it's returning nested to pulse with cell location now this is expected because uh we have seen that in here to extract a value we need to use dot value method but in here if i use dot value here and rerun my program again it will throw an error tuple object has no attribute value a reason for this is because this is an asterisk so we need to unpack this nested tupper to extract the value for each component to do that we can simply use for loop so i'll remove this here few things and now you can see here in big tuple let me copy this here so we can sit better let me break this down real quick all right this big tuple has four smaller tuples and each tuple has two values okay so we need to specifically target these two values and run dot value method on each of that and extract the value so we need to run this in a using a for loop an extract value for each cell to do that let me comment this out we can use for loop so i will use tuple unpacking here for a and b in value range what this will do is for each tuple it will give me a and b separately here i can do that in multiple for loop or nested for loop but i know python provide very easily tuple unpacking so we can do that directly so this will go as a and this will go as a b okay in here i can simply say print a dot value and b dot value now let's rerun our program and there you go here you can see we got john his balance bob his balance skin his balance team and his balance we started from this e2 shell and we get value all the way till around this 1400 you can see that here in our python program that's how we can target particular range of values using python thank you everyone and i'll see you in the next demo

Channel: Python Bits

Views: 18,435

Keywords: learn python, python programming, python, python for beginners, read excel in python, write excel in python, write excel in python openpyxl, read write excel in python, excel read and write in python, openpyxl tutorials, read excel files in python, Python How To Read Microsoft Excel Files, microsoft excel python, excel python, python xlsx, python openpyxl, openpyxl tutorial

Id: iGYdD1W0WPE

Length: 6min 45sec (405 seconds)

Published: Fri Jan 01 2021