Extract Data from an Excel using Power Automate Desktop

A few days ago, I was working on a Power Platform project that required me to extract data from an Excel file based on a specific condition using Power Automate Desktop.

You can achieve this using PAD. In this tutorial, I will explain how to extract the data from an Excel Worksheet using Power Automate Desktop with the two examples below:

• Extract Excel data to a text file
• Extract Excel data to a text file based on a condition

Extract Data from Excel to Text File using Power Automate Desktop

If you have the Excel file with data on your local system, you can use it to extract the data using PAD.

To explain this example, I will use the Excel File named ‘Employee Details‘ with the following data. I wanted to extract this data to a text file. Have a look at the image below to know what columns are available in the Excel sheet, including the data.

Here are the steps to get the data from an Excel file using Power Automate Desktop:

1. Open the Power Automate Desktop App -> Click on + New, then select Flow. Provide the flow name and click on Create.

2. First, to open the Excel file present on your local desk, expand the Excel connector under the Actions pane. Then, drag and drop the “Launch Excel” action to the workspace. Provide the below information.
   • Launch Excel: Choose and open the following document option.
   • Document path: Click the insert file icon and select your Excel file.
   • Make instance visible: You can enable this option to display Excel on the taskbar during flow execution.

This action will produce a variable named ExcelInstance. After that, click on the Save button.

3. Next, to retrieve the first free column or row of an active worksheet, I will add the “Get first free column/row from Excel worksheet” action. This will be useful to extract the End columns of an Excel sheet dynamically.
   • Refer to the image below that shows the end columns and rows.

4. Then, search for ‘First free column’ under the Actions pane. Drag and drop the “Get first free column/row from Excel worksheet” action. It will produce two variables, i.e., [FirstFreeColumn and FirstFreeRow]. Save the action to the workspace.

5. To extract the data from an Excel sheet, drag and drop the “Read from Excel worksheet” action that is present under Excel. Enter the details below:
   • Excel instance: By default, it will take the Excel variable.
   • Retrieve: I have selected the “Values from a range of cells” option.
   • Start column: Enter the value as 1.
   • Start row: Provide value as 1.
   • End column: Select FirstFreeColumn variable -1, because this will give the end column dynamically.
   • End row: Choose FirstFreeRow variable -1. This will get the end row dynamically without hardcoding it.

This action will produce the ‘ExcelData’ variable, where the Excel data will be stored. Click on the Save button.

Now, we have extracted the data from an Excel Worksheet.

6. To display the extracted data, I will add the “Write text to file” action. Set the properties as below. Then, save the action by clicking on the Save button.
   • File Path: Click on the Select file icon -> Choose a file. Here, I have selected a text file.
   • Text to write: Tap on {x} -> Select ExcelData variable from previous step.
   • Append new line: Turn on the toggle button.

7. Now, save the flow and click the Run button to execute it. 

8. After the flow execution, you can see that the text file is filled with extracted data from the Excel sheet.

This way, you can extract the data from the Excel spreadsheet to a text file using Power Automate desktop.

You can check: Extract Data from Word File to Excel File

Extract Data from Excel using Power Automate Desktop [Based on Condition]

In the next example, I will show you how to extract data from an Excel worksheet to a text file based on a specific condition using Power Automate Desktop, as you might not need to extract all the data.

Here, I will use the same Excel file as in the above example. I want to extract the data of employees into a separate text file where the salary is greater than $80000.

Follow the steps below. I will continue the above flow here.

1. Following the “Write text to file” action, search and add a For each loop action. This will iterate over the list of items in an Excel sheet. It will produce CurrentItem as a variable.
   • Value to iterate: Select the ExcelData variable.

2. Inside a For Each loop, add an If Conditional action that filters the data from Excel for items with a [Salary] value greater than 80000. Provide the information below. Click the Save button.
   • First operand: Select the CurrentItem variable
   • Operator: Select the Greater than operator.
   • Second operand: Enter the value.

3. Inside the IF action, drag and drop the “Write text to file” action. Provide the information.
   • File Path: Choose the document file path from the select file icon.
   • Text to write: Add CurrentItem variable.
   • Append new line: Toggle on the button.
   • If the file exists: Choose the Append content option if the file already exists. It will append the values to the existing text.

4. In the final step, add the “Close Excel” action and click on the Save button.

5. When the flow is ready, save and run the flow. After the flow execution is completed, you can see that the extracted data from an Excel worksheet will be added to the file. Have a look at the reference screenshot.

This way, you can extract data from an Excel worksheet based on a condition using Power Automate desktop.

Conclusion

In this tutorial, I have explained how to extract data from an Excel file using Power Automate Desktop. We also saw two real examples: one to extract all Excel file data into a text file using PAD and the other to extract Excel file data based on a condition using Power Automate Desktop.

Please let me know if you still have any questions; I’m always here to help.

You may also like the following Power Automate tutorials:

• Power Automate Desktop Flow Examples
• Connect Power Automate Desktop to Cloud Flow
• Extract Tables from PDF by Power Automate Desktop
• Insert Row into Data Table in Power Automate Desktop
• Get Values from Data Table using Power Automate Desktop

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.