How to loop through each row in an Excel file - Power Automate Desktop Excel actions tutorial

hello everyone welcome back to the ms powell automate channel today we are going to discuss how to look through each row in an excel file i believe this scenario is a very common scenario to a lot of our viewers here because you know as we know we commonly use excel file to store data whether at work or in school right so for today's discussion what i have is the following excel file which contains several columns of data such as first name last name company name and so on and so forth and in this excel file i have a total of 10 rows of data so now the question is how am i able to use microsoft power automate desktop to look through each of this row of data and in retrieve the individual values right turns out that the solution is not too complicated right we're going to actually walk you through two different ways or two different solutions which you can use to look through the excel file right so let's get started right let me go to my flow designer and let's talk about the first solution right so first of all i would need to attach to a running excel file right because in this case i have already the excel file open so what i'm going to do is i'm going to use the action attached to running excel file and i will simply select the excel file which i have right in in case that if your excel file is not already open then what you will do is you will use the action launch excel file instead right so the next thing i'm going to do is i'm going to use this action called look right or in this case a simple loop you might ask me what what exactly is a look right so a loop is basically a concept in power automate desktop that allows you to repeat one or more actions multiple times and in this case in our case of the simple loop i can actually specify how many times do i want to repeat this action right so remember if we go back to the excel file we have a total of 10 rows of data so essentially i would actually need to repeat the loop 10 times right so what i'm going to do is i'm going to indicate here the starting index or the starting value is actually 2 and in this case this value 2 actually corresponds to the second row of the excel file which contains actually the first row of data right and i'm going to terminate this loop at row 11. and along the way for each loop i'm actually going to increment the the loop value or what we call the loop index by a value of one right i'll click save and then now what i need to do is i need to specify as the flow is looping through each of this row what do we want the flow to do right so in this case what we want is we want the flow to actually read from the excel file right and in this case we want to read the or we want to retrieve the value of a single cell and i will need to indicate the start column and the start row so for a start let's try to get the value of the first name right so what i will do is i will specify the start column to be a or alternatively if i want i can also specify the stack column to be one which basically corresponds to the a column right so let me use a and in this case the start row i would need to specify not the value 2 but the loop index right this is important because as we go through each iteration of the loop thus the row actually changes starting from 2 3 4 5 onwards all the way until we reaches 11 right and in this case we would also want to change the variables which is produced right this is where the first name data is stored so in this case to make it more intuitive i will change the variable to be first name i'll click save right so for illustration purpose i will repeat the same step right and i will read from also from the second column or column b i will specify the loop index and in this case the variable produced i would save it into a variable called last name right finally to make it easy for you to view the output i would use a message display message action to display the message right so let me call this title excel and let me put in some message the current loop index is loop index the first name is first name the last name is last name right and let me click save right so i'm done now let me run the flow and let's look at the results right so what has happened what has just happened is that now the flow has went through the loop right and it's going through the first iteration of the loop so in the first iteration the current loop index is actually 2 which corresponds to the row number 2 over here right and you can verify that the first name is john the last name is smith right once i click ok the loop will go on to the second iteration of the loop so in this case you can see that the current loop index is actually three right and the first name is jane and the last name is dorsey right so let me click ok to continue this and you can see the results as we go along right so we have come to the last iteration of the loop so the current loop index is 11 right and we can see that the first name is lara and the last name is palmer right so after i click ok right the flow actually breaks out of the loop because we have set the end and look to be value 11 and we have successfully completed the entire flow right so this is the first uh way of looping through an excel file in this case basically we are using a simple look to look through the excel form now let's look at the second method how we can look through each row in the excel file for the second method we're going to make use of a concept called data table a data table is basically a variable that stores data in the tabular form pretty much like how you will store data using an excel file right so let's see how the second method looks like okay first what i'm going to do is let me delete all the all the actions that we have created earlier right so what i'm going to do first is i'm going to go to my excel action and i am going to use the read from excel worksheet right now in this case instead of retrieving the value of a single cell what i'm going to do is i'm actually going to retrieve the values from the range of cell right and in this case my starting column is a my starting row is row 1 and my ending column is column g right and my ending row is 11 right and in this case remember to expand the advanced session and make sure that you select first line of range contains column names right what we are telling what i was saying is that the first row in the excel file is actually does not contain data but it actually contains the column header right so i'll click save now if i run this flow you can see that now and now let me just double click on the excel data you can see that the flow has actually extracted all the values from the excel file and then store it into this variable excel data which is nothing but the data table right and we have our 10 rows of data right if earlier on if i did not specify that the first line of range contains column names and let me run this flow again what you will observe is that now inside the variable excel data it actually contains 11 rows and not 10 rows of data and you can see that you know the first row actually takes the value of the column either firstly last name and so on and so forth which is obviously wrong in our example over here so what i'm going to do is let me go back and change it to the correct settings okay let me just run this again and the other thing that i want to point out is that when we are using data table it is important to remember that data table uses a zero base index what do i mean by this right if you look at the data table over here the first row of data does not start with the index value zero it starts written so it does not start with the index value one but it starts with the index value zero the second row corresponds to the index one third row corresponds to index value two and so on and so forth right and this also applies to the column right so unlike excel file where the first name is column 1 or column a the first name column in the data table actually corresponds to the column value 0. the last name corresponds to the column value 1 and so on and so forth right so this this distinction is important because later on when we want to retrieve the value from the data table we need to make sure we use the correct index right so now after we have read the data into a data table what we will do next is we will make use of the loop right and in this case we will not use a simple loop but we will use a for each loop right so for each loop is useful when we want to integrate over something like a data table or a list right so here what i'll need to do is i'll need to specify what is the data table i want to iterate over which is the excel data and you can see that it will basically look through the data table and for each of the iteration of each of the loop it will store the data inside this variable called a current item right and this current item is none other than a data row containing all the values corresponding to that particular row in the data table right so now after i've done this what i'll need to do is i will maybe again i will use a display message action to show you the outcome right so again i will indicate the first name is and in this case right the first name that we want to display is the current item right but the current item contains all the data from the first row right you have the first name last name and so and so forth so i only want to display the first name so the way to retrieve the value of the first name is by using square bracket index 0 and another square bracket right so that will give me the value for the first item in the current item order in the current data rate right so likewise the last name is again current item and in this case because the last name is the second column so the index value is one okay let me click save and now let me run this flow to see the output right so you can see that in the very first iteration of the data table the first row corresponding to index 0 you retrieve the first thing as john smith which if we verify against the excel the excel table it is correct right so let me click ok and then it will go through the second iteration and it will retrieve the first first name and last name to be jane dorsey right and so on and so forth right so we have gone through two methods of actually looping through each row in the excel call to retrieve the values right do you think there is a third solution or third method please feel free to leave your comments in the comment box below i'm very interested to see whether any one of you has any ideas on how we can look through the excel file using other methods and if you if you have any particular topics which you want us to cover please drop us a comment below and we would uh definitely look into it right that's it folks that's all i wanted to share with you today i hope you find this video useful and please do subscribe to the ns power automate channel to receive the latest update on microsoft power automate desktop including learning tutorials tips and tricks see all of you soon and take care bye

Channel: MS Power Automate

Views: 1,859

Rating: 5 out of 5

Keywords: microsoft power automate desktop, microsoft power automate, power automate desktop, power automate, power automate desktop tutorial, power automate tutorial, power automate desktop for beginners, excel automation, power automate desktop excel

Id: NmSCxexNItE

Length: 16min 42sec (1002 seconds)

Published: Thu Jul 22 2021