Excel Automation in Power Automate Desktop | Read data from Excel in Power Automate Desktop #02

hi guys welcome back to the session today i am going to explain multiple scenarios to read data from an excel file using power automate desktop so let's get started let's consider our first scenario to read this complete data from this excel file and store it in a text file so let's build this project in power automate desktop so for that i'll move to power automate desktop and we'll create a new flow from here let's give a flow name excel demo and hit on create now here we'll add the actions to create our flow and to read data from this excel file first of all excel need to be launched so i'll add the required actions here so from this excel i'm going to use this launch excel action so let's drag and drop it to the main panel now here we'll have to provide some parameters first parameter is to launch excel here we have two options to launch excel that is launch excel with a blank document or launch excel with an existing document now since we wish to work on this specific document we will go with the option open the following document and will provide the document path of this file so click on this select file and from here i'll choose this excel file and let's click on open now the next parameter is to make the excel instance visible so during runtime if you wish to make the excel instance visible turn this on otherwise turn this off so as of now let's go with this on option we wish to make the excel instance visible during the run time next if you wish to open the excel in read-only mode turn this on otherwise turn this off next we have some advanced options as well if your excel is password protected provide your password here if you want to load add-ins and macros turn this on otherwise turn this off so these were the advanced options now this excel launch excel action produces a variable excel instance this excel instance will be used in further excel actions this variable identifies that the specific excel actions will be used for this specific file this specific excel file so all the details has been provided under this launch excel let's hit save now we are good to read the data from this file so for that i'm going to use read from excel action let's drag and drop it here and here you see this excel instance variable which got created in the first action excel instance variable got created in the first action which is also visible under the variable section it is a flow variable which got created this excel instance is visible in under the read from excel action as well this signifies that the data will be read from this specific excel so this is the excel instance variable next we have some options to retrieve we can retrieve the value of a single cell value of range of cells or values from selection let's go with the option values from a range of cells since we wish to extract this complete data next we'll have to provide start start column start row in column and in row so in our case the start column is column 1 and start row is row 1 so here we'll provide start column as 1 start row as 1. next let's check the in column and row here in our case the end column is 4 and end row is 7 so here we'll provide accordingly in column as 4 and in row as 7. now this data which we have provided for column and row values will work fine if you have this fixed number of rows and columns but what if in future the number of rows gets increased or the number of columns gets increased due to increase in data in such case this value which we have provided here will not extract the exact data so how to take the exact data so in such case we'll have to make the end column and end row as dynamic in nature so how to do that let me show you for that we'll have to add another action let's save this as of now and before this action i'm going to use the action get first free get first free column and row so let me add it here after the launch x selection this action also takes the excel instance variable that is that means it is going to work on this specific excel sheet so the excel instance variable it has taken and this action produces the variables first free column and first free row so it's going to provide the first free column and first free row from the excel sheet so let's hit save and this first free row and first free column got created here now we'll move to read from excel worksheet again and instead of this hard coded in column and end row what we are going to do will select a variable from here as first free column minus 1 so what does it mean so as of now since we have only four columns the first free column the value of first free column in this case will be 5 and we wish to read minus 1 that is still 4 so here we have written first free column -1 in a similar way we'll provide the value for end row as well so from variables we'll select this first free row in our case the first free row is 8 and we wish to read till 8 minus 1 that is 7 so here we'll provide first free row minus 1. and with this we have made this in column and end row value as dynamic in nature whatever the value of end column and end row will be present in the excel will be reflected here now moving to the advanced option if you wish to get the cell content as text if you wish to get all this cell content as text turn this on otherwise turn this off so my oneness1 has been removed let me add it here and the next option is it is asking if the first line contains column names so yes in rxl5 the first line is the column names these are not the test data these are the column names so here i'm going to turn this option as on and this action also produces a variable excel data which is a data table so this complete data will be read from this excel file and stored inside a data table variable that is excel data so all done for this action as well let's hit save and once we read the data from this excel we are good to close the excel no job of excel is left as of now so let's move to excel again and here i'm going to use this close excel action it has taken the excel instance variable and here it has provided some options also to save or not to save the document since we are not doing any modifications saving a document is not required so we'll go with this option do not save the document so all the details has been provided let's hit save so as of now we completed all the steps to read the data from this excel file now let's move ahead to write the data from this text file so for that i'm going to use another action which is called write text to file so let me add this action here and here we'll have to provide the file path of this file basically the file path of the file on which we want to write so from the select file let's choose this text file from here let's hit open and what text we want to write we want to write the text excel data which holds this complete data from this excel file as a data table variable so here from the select variable option i'm going to choose this select data of type data table now here also we have the option to append new line at the end of the overall text to write to the file so you can turn this on or off according to your requirement next it also provides the option to override the existing content or append contact so in our case i am going to go with this override existing content so if the file exists we are going to overwrite the existing content also it provides several encoding options so we'll go with this default one that is the unicode so with this we have provided all the details under this action as well let's hit save and with this our flow is also complete let me save this flow and let's hit ok so with this in this flow we are going to read the data from the excel file and write it to the text file let me add a message box as well to show you the flow so here i'm going to use this display message before this close action let me provide the message box title as message and here i am going to display the message as data read from the excel file closing the excel file now let's take the message box icon as information and message box button as ok let's hit save let's save this flow one more time and we'll quickly run this flow to see how it works let me close this excel file and let me close this text file as well and now let's run this flow to see how it works so the execution is in progress the excel got launched the data got read from the excel and a message got displayed data read from the excel file closing the excel file now so data has been read and now once i close this message box this excel will be closed and after that the data will be written to the text file so let's hit on ok and if the flow is over let's quickly move to this text file to see the data which got written and this is our text file let me open this one and here you see the entire data got written from the excel file to this text file let me open this excel file as well so this complete data got written from this excel file to this text file now here if you notice this column names are not written from this excel to this text file why did not get written let me show you so if you move to this read from excel worksheet action here in the advanced section this option that is first line contains the column names we have done this on that means this first line is the column names and not the data now since this first line is the column name and not the data that's why this excel data variable do not hold this first line it only holds the data which starts from here so that's why this complete data got written to this text file and with this we created the flow for our first scenario successfully in par automate desktop we read this complete data from this excel file and written to this text file now moving to the next scenario let's say that we want to write only those data from this excel to this text file where the salary is greater than 10 000. so let's see how we can create this flow in power automate desktop so in this case also let me close this one in this case also the reading data from the excel file will be similar that means all these steps will be similar only the writing the data to text file will differ so let me delete this action and we have this excel data variable which contains this complete data from this excel file now since we wish to write the data into the text file based on the salary so we'll have to iterate each of these rows one by one each of these rows will be iterated one by one and we'll check whether the salary is greater than 10 000 or not so let's see how we can do this in power automate desktop for that let's cancel this one and here i'm going to use the action for each let's add this for each action and what value we wish to iterate so from the select variable we are going to iterate this excel data which is of type data table so we are going to iterate this excel data variable which produces this current item variable which is a data row variable so let's hit save and for each of these rows in the excel data variable we want to check the value of salary so here i am going to use the if condition let me add this if condition here and here will have to provide the operands and the operator so since we wish to check the salary so here we'll have to use the variable current item and inside the brackets inside the codes i'm going to use this column name that is salary and operator i'm going to choose as greater than if salary is greater than 10 000 if the salary is greater than 10 000 this is the if condition we provided and if this condition is true we wish to write the data to the text file so now we are going to use this previous action that is write text to file so let me add this here and here we'll have to provide the file path so from the select file let's choose this file that let's choose this text file hit open and what text we wish to write so from this select variable we wish to write write the current item which is a data row variable so this complete row a complete row from this excel file will be written to this text file and now if the file exists again it provides two options overwrite existing content or append content in this case we are going to use this append content since the data will be written one by one so we cannot use this overwrite content so let's go with this append content and unicode encoding is fine let's hit save and our flow is ready so let me save this one and let's close this file and let me clear this file the data will be written to this file now based on the salary conditions so let's close this file and our flow is ready let me quickly give you a recap what we are doing so first of all we are reading the data from this excel file and closing the excel next we are iterating through each of the rows of this excel data variable one by one and checking the value of salary if the value of salary is greater than 10 000 then only write the current row that is the current item variable to this text file otherwise skip the row so let's quickly run this flow to see how it works and my execution is in progress excel is launched and data is read from the excel file and the message displayed data read from excel file closing the excel file now so once we click on close this excel file will be closed and the data will be written one by one to the text file based on the salary conditions so the loop is iterating one by one and it is over let's quickly move to the text file so let me open the excel file and the text file as well so here you see only those records from this excel file got written to the text file where the salary was greater than ten thousand so all these salaries are greater than ten thousand here you notice this seventh record and the fifth record got skipped these got skipped because the salary was not greater than 10 000. so in this way we can read data from the excel file and use them further in the project as per the requirement and that's all for this session guys i hope you enjoyed this video and if you did give it a like and share with your friends hit the bell button to get the updates on the latest videos and if you have not yet subscribed to our channel please subscribe to our channel as well and i'll see you soon in the next one bye

Channel: Quality Assurance Lab

Views: 41,229

Keywords: power automate excel tutorial, power automate desktop excel, microsoft power automate excel, excel automation power automate desktop, how to work with excel in power automate desktop, excel power automate desktop, read data from excel in power automate desktop, power automate desktop installation, power automate desktop introduction, power automate desktop setup, power automate desktop flow tutorial, power automate desktop flow, power automate desktop flow examples

Id: eBC3pBEZ5uc

Length: 18min 45sec (1125 seconds)

Published: Wed Mar 17 2021