Power Automate Desktop. How to read data from Excel or to write to Excel? Which DataTable activities
do we have in Power Automate? How to delete, update or insert rows into a DataTable? In this video I will show you the most
important points you have to know, if you want to use Excel or DataTables. Are you ready? Let's Go! In the first chapter I will show you, how to read data from Excel. I have the following Excel file, which I want to read. Here I have a table. It consists of 3 columns and 6 rows. The table is in Sheet1. Let's see how to read this
table in Power Automate. Before start reading the Excel file, let's see which activities
do we have in Power Automate. Everything you need to do in Excel, can be done using these activities. Let's now see how to read from Excel. First thing we have to do, is to connect with Excel. It means, we need to open the Excel file we want to read. For that I will use Launch Excel activity. The first thing we have to fill in is, if we want to open an empty Excel file, or to open a specific file. In my case I want to read specific Excel file, therefore I will select the second option. Now I have a new option for the path. Here I should enter the Excel file path. I only have to write the path. No need to write anything else, like brackets or something else. But you can also use a variable here, if you stored the file path in a variable. Here we have some options, like make Excel visible, or to open the file in read only mode. Here are also other options. The last thing we have in this
activity is the output variable, which we get as output from this activity. It's to store the instance. The name is automatically generated, but you can also change the name here. That's all about this activity. So we want to open the Excel file from this path. The Excel instance will be
stored in this variable. On the right side you can see this variable, and also all variables we have in this flow. In the next step we want to
read the data from Excel. For that I will use the activity: Read from Excel worksheet. First we have to select an Excel instance. Currently there is one selected. But if you have more than one instance
and you want to select another one, you can do it here. Here you can select, what you want to retrieve from Excel. Only a value of a single cell. If I select this option, I have to write the column
and row indexes of the cell, from which I want to read the value. We can also read a specific range. In this case we have to set
the start row and column, and also the end row and column. The third option is to read
the data from the selection. It means, if you selected specific cells before, you can use this option to
read only the selected cells. And the last option is to read the entire sheet. In my case I want to read the entire sheet, therefore I will select this one. We have here some advanced options. For example if we want to get the values as text. It means, to get them as string. If I don't select this option, the number values are returned as numbers, the dates as date etc. And the second option is to define, if the first row contains the column names. In my case it's true, because the first line contains
column names and I want to read them. The output of this activity
will be stored in this variable, which is of type DataTable. So I first open the Excel file. Then I read the entire sheet and
store the data in this variable. The last thing we have to do, is to close the Excel file. For that I will use Close Excel. Here also I need to select the instance, which I want to close. Before closing the Excel file, I can select one of these three options. Either to close the file without saving anything. Or to save the file and close it. Or to save the file, but using a new name or new path. In my case I want to read data from Excel. So I don't write or change anything in the file, therefore I only have to
close the file without saving. These are the three steps, which we need to read data from an Excel. Let's now run the process to check the result. It's done! Here on the right side you can
see the content of the variables. I have an Excel instance in this variable. And here is the DataTable, which contains the data from Excel. Let's see the table. As you see, I have 3 columns and 6 rows. This is my table, which I got from Excel. The first column here is automatically generated. It's not a column in the table, but only to show us which
index do we have in each row. So the indexes of the table start with number 0. It means, the first row has the index 0. The second row has the index 1, and so on! This is how to read data from Excel file, which contains only one sheet. Let's open the Excel file. I will create a new sheet. It's Sheet2. This sheet contains no data. It's empty. I will save the file and close it. Let's run the process again
and check what happens now. As you see, I got an empty table, which contains no rows, and one column. This happens, because I am reading the data from the worksheet, which is open currently. I was in Sheet2 before saving the Excel file. Therefore I got the data from this sheet. But I don't want to read the data from Sheet2, but from Sheet1. So I have to define, from which worksheet I want to read the data. For that I will use the following activity: Set active Excel worksheet. First I have to select the instance. I only have this one. I can activate the sheet either using the name, or using the index. Let's use the name. And here I will write the sheet name, which I want to activate. It's Sheet1. It means now, I open the Excel file, activate the Sheet1, read the data from the activated sheet, and then close the file. Let's run the process again. As you see, I got the correct data from Excel. This table was stored in the
first worksheet in Excel. That's all about reading data from Excel file. Let's summarize the steps, which we have to do once we
want to read data from Excel. We have to open the file at the
beginning and close it at the end. After opening the file we have
to activate the worksheet, from which we want to read the data. After that we can read the
data from the worksheet. We have 4 options to read the data. The first one is to read only one specific cell. The second one is to read a specific range. The third one is to read the selected cells. We have an activity: Select cells in Excel worksheet, which we can use to select
a specific range in Excel, so that we can read it later
using the option read selection. And the last one is to read the
entire data from the worksheet. That's all what you need to know, if you want to read data from Excel. Let's now move on to the next chapter, where I want to show you, how to loop through rows and columns of Excel. We have the table from Excel
in a DataTable variable. Assume we want to do something using the table, like writing the data from the
table into another application. To do that we have to loop the rows and columns. For that I will use the activity For Each. I need to set a variable for this activity, which I want to loop through. In my example I want to
loop the DataTable variable. So I will select the variable here. This activity returns the following variable, which contains the current row. It means, in each iteration of the loop, I am storing the current row in this variable. So I can use this variable later to
read the data I want from the table. I will add Display Message activity, to return the content of specific
cells from the table in popup window. Let's set the following title for example. And here I want to display the ID of each row. For that I need the CurrentItem variable, which contains the current row. Now I have to define, which column content I want. As mentioned, I want the content of the column ID. So I will add the column name in this way. This expression returns the
content of the column ID. So I am using the column
name with the row variable. This is how to use this variable
to get a specific column content. Now I will run the process to show you the result. So first I got the data from Excel. After that in the loop I am
getting the content of column ID. Here is the first ID from
the first row in the table. This is the second ID from
the second row in the table. And so on I will get the content
of column ID from each row. Let's now get the content of
each column from the table. For that I will copy the
expression and add it again here. I only need to change the column names. I first have the content of column ID. After that I added a space. Then I have the content of column Name. Then I have a space again. At the end I have the
content of column Department. For example the output for the
first row should be as following. So I will get the content of each column. It means, if you want to get the content of any column, or the content of different columns, you have to set the expression as I did here. Let's run the process again
to check the new result. As you see, I have the ID, an empty space, the Name, an empty space, and then the Department. The same is also here. And so on I will get the content of
each column from each row of the table. I used here the name of each
column to get the content. But I can also use the index of the column. The first column in a table
has always the index 0. The second column has the index 1. And the third column has the index 2. As you see, I set the indexes without
anything within the brackets. It means, I don't need to write semicolon here, as I did for the names. I only have to write the index. I will add this symbol here
between the content of each column. Let's run the process again. As you see, I got the content of each column again, with the symbol between the different columns. So it works also fine! This is how to loop through rows and columns. Let's now see how to use the rows
and columns to set a condition. For example I will add If
activity to get only the rows, where the Name is equal to D. In this activity we have to set
the first and second operand, and also the operation between both. As mentioned, I want to get only the rows, where the name is equal to D. It means I first need the content of column Name. I can get it using this expression, as I showed you before. The name should be equal to D, so I will set this name here. The operation is equal. I have here different operations, which I can use in case I
want to check something else. Let's move this activity inside the If activity. The loop is now to go through
all rows of the table. I will check each row, if the name is equal to D. If it's correct, I will get a popup window with
the content of each column. Let's run the process to check how it works. As you see, I got this message where the name is D. The execution is finished
and I got only one message, where the name is D. This is also how to use a condition
within a loop of DataTable. Let's now summarize the points from this chapter. To loop a DataTable we need a loop activity, for example For Each. The variable of this activity is of type DataRow. This can be used to get the content
of the table from a specific column. It can be used as following. We can either use the name of the column, or also the index of the column. The index of the columns starts always from 0. It means the first column has the index 0, the next one has the index 1, and so on. This is how to loop through
rows and columns of a DataTable. Let's now move on to the next chapter, where I want to show you, how to delete row from a DataTable. In Power Automate we have under
variables some activities for DataTables, which we can use to modify a DataTable. Let's see one of them: Delete row from data table. We need two inputs for this activity. The first one is the DataTable, from which we want to delete the row. And the second thing we have to set, is the index of the row we want to delete. Currently I don't have row index. I could set a number here, to delete a specific row. But if we want to do that dynamically, we have to create a variable for that. Let's see how to do that! First I will create a new variable. I will set the following name. And here I will set a value. I will set the number -1. Why I set this value, you will see that later. Now I have a variable of type integer
with value -1 before the loop. I want to use this variable inside the loop. So I will add the activity: Increase variable. I want to increase the RowIndex variable. I want to add one in each iteration. It means here, that the variable RowIndex has in
the first iteration the value 0, which is -1 plus 1. This number is the index of the first row. And in each iteration I have plus 1, which is also the index of the next row. It means, I have now the index of the
current row in each iteration. Now I can use this variable
to delete a specific row. I can now use the activity: Delete row from data table. I only want to delete the row, where the name column is equal to D. First I will set the data table variable, from which I want to delete the row. And here I will set the index of this row. It means now, that I want to delete a row from this table, which has this index. Let's run the process to check the result. It's done! Let's check the table. As you see, I have all rows in the table, except the row where the name is D. So it works fine. Let's add a new condition to delete another row. For example I will use Else If. I want to delete the row, where the name column is equal to E. So now I have two conditions. If the name is equal to D, the row will be deleted. Or if the name is equal to E, the row will be also deleted. Let's run the process to check
the result if it's correct. Let's check the table again. As you see, the row with the name D is deleted. But I still have the row with the name E. And the row with the name F is deleted. So here is something wrong. Let's see how to fix it. As you know, I used the variable RowIndex to
calculate the index of the current row. But inside the loop I am deleting some rows. This means that I will have a wrong index, once I delete a row from the table, because the index is increased. So I need to decrease the RowIndex
once I delete a row from the table, so that I have the correct index. For that I will use the following activity. I want to decrease this variable. It means now, once I delete a row from the table, I will decrease the variable. In this way I will have the correct index always. The same should be added at this point, because I also delete rows here. Let's run the process again. I will check the table. As you see, I deleted 2 rows from the table. And these are the correct rows, where the name is D or E. This is how to delete a row from DataTable. Let's summarize the steps of
deleting a row from a DataTable. To delete one row from a DataTable, you can use the activity: Delete row from data table. For this activity you need 2 inputs. The first one is the DataTable, from which you want to delete the row. The second one is the index of the row, which you want to delete. You can write a number directly as index, or you can use a variable for that. In case you have a loop and
you want to use a variable, you have to use the following 3 activities. Set variable to create a
variable and set a default value. Increase variable to increase the
content of this variable inside the loop. Decrease variable to decrease the
variable once you delete a row, so that you still have the
correct index inside the loop. That's all about deleting a row from a DataTable. Let's now move on to the next chapter, where I want to show you, how to update a row inside a DataTable. Under DataTable activities we
have the following activity, which we can use to update
an item inside a DataTable. It means, we can use this activity to update
only one cell inside a table. Let's use the activity to
update one cell in our table. The first thing we need is the table, in which we want to update an item. The next one is the column. Here we can either write the column name, or we can use the column index. I will use the column name. For example I want to update
an item inside the column Name. The next one is the row. Here we can also use the index or the name. I will use the index here to make it dynamically. For that I will use the variable RowIndex, which contains the index of the
current row inside the loop. And the last thing we need is
the new value for this item. For example the following value should
be added instead of the current one. This activity means now, that I want to update an item in this table, at the following row index, in the following column, and here is the new value. I will disable the following action, because I don't want to delete the row. And also this one. The code means now, that I want to update only the row item, where the Name is equal to D. Let's execute the process to check what happens. Let's check the table. As you see, I have everything as I had before. Only the following value is updated. So I replaced the value D with this new value. This is how to update only one item. Let's see how to update more than one column. For that I need the same activity again. I only have to change the parameters. So now I want to update the content of column ID. This is the new value. Let's do the same also, but for the third column: Department. I want to set this new value. So now I will replace all
column values for only one row, where the name is equal to D. Let's execute the process
and check the new result. I will check the table. As you see, I have new values in all columns of this row. Here is the new ID, the new Name, and also the new Department. This is how to update an item inside a table. Let's now summarize the points from this chapter. To update an item inside a
table we need the activity: Update data table item. For this activity we need 4 parameters. The table, in which we want to update. The column name or index. We can write the name of the column, or we can use the index of the column. For the index we can write a number directly, or we can also use a variable of type integer. The row index. Here we can either write a number directly, or we can use a variable of type integer. And the new value to replace the old existing one. That's all you have to set to
update an item inside a table. Let's now move on to the next chapter, where I want to show you how to
find a specific value in a table, and also how to replace it. It means, I will use an activity to search for a value. I can do the same using a loop, as I did in the last examples. But for now I will use the activity: Find or replace in data table. Let's first disable all these activities, because I don't need them now. I will use this activity. Let's set it at this point. Let's see which inputs do
we need for this activity. The first one is the table, where we want to search. I want to search in this table. The next one is the search mode. Here we have two options. The first one is to find something. The second one is to find
something and also to replace it. Let's first use the option find. The next thing we have to set is the following. This means, if I want to get all matches from the table, or only the first match. Currently it's enabled, which means that I want to get
all matches from the table. And here I have to write the text, what I am searching for. For example the following text. I will show you these three options later, what does each one mean and how to use. And the last thing we have here
is where we want to search. Either we can search in the entire table. Or we can search only in a specific column. Let's search in the entire table. From this activity we have an output. It's a data table variable, which contains the indexes of the found matches. I will also disable this activity, because I don't need it anymore. Let's now execute the
process and check the result. Let's check the output. As you see, I have the following table, which consists of 2 columns, Row and Column. This table contains the row index
and column index for each match. Each row contains the indexes for one match. As you see, I have only one row in this table, which means that I have the value I am
searching for only once in the table. Let's check the table. As you see, I have the text D only once in the table. So it's at row index 3 and column index 1. Let's now change the text we are searching for. I will write the same text, but in small letter. I will execute the process again. Let's check the output . As you see, I got the same result again even
though I wrote the text in lower case. It means, it's not a matter which text case do we have. Let's check the activity. Here I have the following option: Match case. It's disabled now, which means that I want to search
for the text without case sensitive. Let's enable it. Now it means, that I want to search for exact the same text. So it's case sensitive now. I am searching now for exact
the same text in lower case. Let's execute the process to check the result. As you see, I have no results now, because the text in lower case
doesn't exist in the table. Let's see another scenario. I will search for the text C in upper case. And I will disable Match case option. Let's execute the process. I will check the output. As you see, I got more than one match. It means I have the text C 4 times in the table. Let's check the table. As you see, I have the text C in these 4 positions. It means we are searching for
the text within other text. It must not be alone in a cell. Let's check another scenario. I will disable the option to get all matches. It means now that I want to
get only the first match. I will execute the process again. Let's check the result. As you see, I have only one row as output. This is the first match, where we found the text C in the table. It's this one. This means, the search is working in this way. We start in the first row in the first column. Then we move on to the next column and so on
until we searched all columns for the first row. Then we move on to the next row and do the same. This is very important to know, if you want to get only the first match, so that you know how it works. Let's check another scenario. I will enable All matches again, to get all matches from the table. We have here an option to
check the entire cell content. It's currently disabled, which means that we are searching for the text, no matter if a cell contains only this text, or also another text with the
text we are searching for. I will enable this option. In this case I want to search for only the cells, which contains only the same
text without any other text. Let's execute the process and check the result. I will check the result. As you see, I have only this row where
the text is exact the same, what I am searching for. You can check it here in the table. Only this cell contains the text. Let's check another scenario. Now I will use regular
expression to search for a text. For that I will write the following expression
to get only the numbers from the table. And here I will enable the following option, that I am searching using a regular expression. Let's execute the process to check the result. Let's see the output. As you see, I have many different matches. Let's check the table. As you see, all matches are in the first column. So each row contains a number in the first column. This is how to search in a table
using a regular expression. Let's check the last scenario for this activity. Now I want to find a text and replace it. For this I have a new field, where I can set the new
text to replace the old one. I will write the following text. So in this case I will search for all
numbers using the regular expression. All matches should be replaced
with the following text. Let's execute the process again. Let's first check, if we found matches. As you see, I got the same result again. Let's check the table. As you see, I don't have any numbers anymore. I only have the new text, which I added using the activity. It means all numbers are
replaced with the new text. This is how to use the activity to
find or replace a value in a table. Let's summarize this activity. The following parameters and settings
are needed for this activity. The first one is the table, where we want to search. The second one is the search mode, if we only want to find a value, or also to replace the found value. Next one is to define, if we want only to get the first match, or also to get all matches. After that we have to set the
text we are searching for. Here we can either set a
text or a regular expression. If we write a regular expression, we have to enable this option. But if we write a text, we don't need to enable this option. The next one is match case
to search in sensitive case. The next option is to define, if we want to search for the cells
where the text is exact the same, or if we want to search for the
text also inside other texts. It means, we either searching for a cell
which contains only the text, for example this one. As you see, the cell here contains only C, what I am searching for. Or also to search for the text inside other texts, for example this one. As you see, the cell contains C, what I am searching for, but also other text. And the last option is to define, where we want to search. Either in the entire table, or only in a specific column. These options are also the same, if we want to find and replace a value. We only have one option more, if we want to replace the value. It's the new value which should be
added instead of the old found one. These are all settings we have to set to
find or replace a value inside a table. The output of this activity is
a variable of type DataTable. This table contains always two columns, Row and Column. These columns contain the
indexes of the found matches. That's all about this activity. Let's now move on to the next chapter, where I want to show you how to
insert a row into data table. Let's first disable this activity, because I don't need it anymore. I will use this activity to insert a row. We need here the following three parameters. The first one is the table, where we want to add the new row. I want to insert a new row into this table. The second one is to define, where we want to insert the row. Either at the end of the table, or before a specific index. Let's try to add the new
row at the end of the table. And the last option is the row. Here we either can add the value directly, or we can use a DataRow variable. Let's add the new row directly
here without a variable. To add the value directly, we have to set it as following. Please make sure you add a value for each column. It means, the table contains 3 columns. In this case I have to add three values, so that I can add the row. I added a text for each column, and I added three values for the three columns. Let's save it and run the
process to check the result. Let's check the table. As you see, I have the same rows from the Excel file. At the end of the table I have this new row, what I already inserted. So it works fine. Let's now try to add this
row before a specific index. Once I selected this option, a new field appears to write the row index. Here I can either write the index as number, or I can also use a variable for that. Let's write a number, for example 3. This means now, that I want to insert the
new row before the index 3. In other words, I want to insert the row at the index 3, and the old row at index 3 will be now at index 4. Let's run the process to check that. Let's check the table. As you see, I have the new row at index 3. The old row at index 3 is now at index number 4. This is also how to insert
a row at a specific index. As you see, I added a new row with values directly. Let's now try to add a new row, but now using a variable. For that I will enable this loop. I will move this activity inside the loop. I want to add the new row at the end of the table. Let's now change the values of the new row. As mentioned, I want now to add the values using a variable. I will use the CurrentRow variable. Once I am using a variable, I don't need the semicolon
or the procent characters. I can directly use the variable. In this case I have a variable of type DataRow. Therefore I will use the columns, to get the content of the
table and add it as new value. For the ID column I will use the
content of the ID column from the table. The same also for the other columns. It means, I am copying each row from the table, and adding it again to the table. At the end I should have each row twice. So, I used here the loop variable
to get data from the table, and to add them again to the table. This is only an example here, but normally we could use other variables
to add content to the DataTable. And I added a value for each column. For the ID column I will add the
current content of the ID column. The same for the column Name. And also the same for the column Department. I will save the changes and run the process again. Let's check the DataTable. As you see, I have now more rows in the table. I have the first row here. And the same below. The second row here. And the same below. And so on I added each row from
this table again to the table. This is how to insert a row into
a DataTable using a variable. Let's now see, how to add an expression using the variable. I want for example to add the
content of column ID into column ID, but after multiplying with number 5. This expression means now, that I am reading the content
of column ID from the table. After that I multiply it by number 5. The result of this expression will
be added into column ID of the table. The other columns are the same. Let's save and run the process
to check the new result. Let's check the table. As you see, I have again the same rows twice, I mean each row twice. But the ID's are now different. They are multiplied by 5. This is how to use the activity
to insert a row into a table. Let's summarize this activity. To use the activity: Insert row into a data table, we have to enter three parameters. The first one is the DataTable variable, where we want to insert the row. The second one is the location of the new row, where should it be inserted. Either at a specific index, or at the end of the table. The last one is the row, what we want to insert. Here we can either add the row as variable, like this one here. Or we can add the row with values directly, like this one here. Please make sure, you write the syntaxes in the correct way, else you will get an exception. Please also make sure, you add a value for each column, else it will not work. That's all about inserting
a row into a data table.
Let's now move on to the next chapter, where I want to show you
how to write data in Excel. After editing the DataTable
we want to write it in Excel. To do that we have to follow these steps. First we need to open Excel file. For that I can use this activity. I want to write the data in the same Excel file, therefore I will select this option. And here I will enter the file path. The new opened Excel instance will
be stored in this new variable. After opening the file we can write the data. For that I can use this activity. Let's see what we have to enter here. The first one is the Excel
instance of the new opened file. It's stored in this variable. The next one is the value, what we want to write in Excel. Here I have to enter the DataTable variable, for example this one. The next one is where to add the data. I have these two options. I will select the first one, which means that I want to write
the data in a specific cell. If I select this option, I have to set the column and the row, where I want to write the DataTable. For example in column A. And in row 1. This means, I want to write the table in cell A1. That's everything we have
to enter in this activity. The next thing we need to set, is where we want to write the table, I mean in which worksheet. For that I will use this activity here. I will select the Excel instance. And here I will write the worksheet name, where I want to write the table. For example Sheet2. That's all for this activity. This should be before Write activity. The last thing we have to do, is to close the Excel file
after writing the table. Here I also need to select the Excel instance. And this time I want to save
the document before closing, because I entered new data in Excel
and I want to save the changes. These are the main steps, that we need in case we want
to write a table in Excel. Let's execute the process to check what happens. The execution is finished. Let's check the Excel file. I am here in Sheet2. As you see, we have the table in Excel. It's the new table, what we modified before writing in Excel. It consists of 3 columns. But we don't have the column
names in the first row. I will show you how to add
the columns to the table. Let's first delete this table and save the file. To add the column names of the table to Excel, we first have to get these
and store them in a variable. I will use Set Variable activity to
define a variable for the column names. Let's rename this variable to Columns. As value I will set the following expression. The DataTable variable with
the property ColumnHeadersRow. This returns the column names
from the DataTable variable. This variable is of type DataRow. It's only one row, that contains all column headers of this table. So, now I have the columns of the table. Let's add them in Excel before writing the table. For that I will copy this activity. Let's change the content of this activity. The value I want to add here
is the Columns variable, that contains the column headers. This should be entered in cell A1. Now I have to change the
cell in the next activity, where I want to add the table. I will set A2 instead of A1, because A1 will contain the column headers. Now the flow is ready. Let's execute the process again. The execution is finished. I will check the Excel file. As you see, I have in the first row the column headers. After that I have all rows
from the DataTable variable. This is how to write a DataTable in Excel. Let's summarize the steps to
write a DataTable in Excel. First thing we have to do, is to get the column headers
and store them in a variable. Then we have to open the Excel file. Then we have to activate the worksheet, where we want to write the data. If we want to write the data in a new worksheet, what we don't have in the Excel file, we can create a new one. For that we can use the activity: Add new worksheet. After that we need to use the activity: Write to Excel worksheet twice. In the first activity we add the column headers. In the second activity we add the DataTable, which contains the rows. At the end we close the Excel file. In this activity we have to
select the option save document, to save the changes. These are the steps we have to do, once we want to write a DataTable in Excel. We are now at the end of this video. I explained the most important activities, what you have to know once you want
to work with Excel and DataTables. There are many other activities, which you can use to read, write or edit an Excel file. I hope you enjoyed the video and could
learn something new and interesting. If you still have any questions about this video, or any other topics, please don't hesitate to write us back. Thanks for watching and have a nice day!
