Power Automate Desktop パワー クエリ

In Power Automate Desktop there are two ways to read an Excel file. The traditional way is to use built-in Excel actions extract all of the spreadsheet’s data. A better way is to create a SQL connection to the Excel file and use SQL queries to get the data we need. SQL queries are twice as fast at reading data from an Excel file and we can write them to filter the data and only return a subset of what we need.

In this article I will show you how to get data from an Excel file using both methods and how to filter Excel data with a SQL query.

Table Of Contents:
• Introduction: The Employees List Excel File
• Create An Employees List Excel File
• Read Excel File Data Using A SQL Connection
• Read Excel File Data Using Excel Actions
• Testing The Speed of SQL Connection vs. Excel Actions To Read An Excel File
• Selecting Specific Excel File Rows With SQL Statements
• Filter An Excel File Where A Column Is Equals A Specific Value
• Filter An Excel File For Values Greater Than A Specific Number
• Filter An Excel File For Values Between Two Dates
• Filter An Excel File To Return Top Rows

Introduction: The Employees List Excel File

The human resources department of a financial services firm has a list of employees and other important details stored in an Excel spreadsheet. They want to use Power Automate Desktop to extract information from the Excel file and input it into their payroll system.

Create An Employees List Excel File

For this tutorial we need an Excel spreadsheet with employee information. Open Excel and type in some sample data as shown in the screenshot above. When saving the spreadsheet make a note of the filename and folder path where it is stored. The path to my spreadsheet is:

C:\RPA\ReadExcelUsingSQL\EmployeesList.xlsx

Read Excel File Data Using A SQL Connection

There are two ways to open an Excel file in Power Automate Desktop. First we will open the Excel file and read its data using a SQL connection. Then we will open it the traditional way using Excel actions and read the data. After trying both methods we will compare them to one another.

Open Power Automate Desktop and create a new desktop flow.

Then insert an Open SQL Connection action.

Then write this code in the connection string field. A connection string specifies information about a datasource and tells Power Automate how to connect to it. Notice that the Source parameter includes a reference to Excel_File_Path variable.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

Now we will write a SQL query to extract data from the Excel spreadsheet. Create an Execute SQL statement step as shown below.

Then write this code in the SQL statement field. This query says to get all of the columns in from the spreadsheet named Sheet1.

SELECT * 
FROM [Sheet1$]

We must add one more action to close the SQL connection once the query is completed.

The full set of actions to open the Excel file with a SQL connection should look like this.

When we run the desktop flow it returns a variable called QueryResult with the full contents of the Excel spreadsheet.

Read Excel File Data Using Excel Actions

The normal way to read an Excel file is by using the Excel actions in Power Automate Desktop. Let’s give this method a try so we can see compare it the SQL connection option.

Insert a new Launch Excel desktop flow action.

Choose to launch Excel and open the following document at the document path stored in our variable.

%Excel_File_Path%

Then add a Read from Excel worksheet action. Use the Excel instance created by the Launch Excel action and retrieve all available values from the worksheet.

Finally, close the Excel file.

The completed desktop flow should look like this.

Run the flow and then look at the QueryResult variable. It contains exactly the same values.

Testing The Speed of SQL Connection vs. Excel Actions To Read An Excel File

Both methods of reading an Excel file return the exact same values. So why is it better to use a SQL Connection as opposed the Excel actions. One advantage is speed. A SQL Connection can read data from an Excel file twice as fast.

The following desktop flow performs both options and uses the get current date and time action to track when they started and ended. The subtract dates action determines how many seconds are between the start and end times and then the result is displayed in a message box.

When we run the desktop flow we can see the SQL connection is twice as faster at reading an Excel file as the traditional Excel actions.

Selecting Specific Excel File Rows With SQL Statements

An even greater advantage of using a SQL connection to read data from an Excel file is we can use SQL statements to obtain a filtered set of rows. If you want to learn how to write SQL queries I suggest using the awesome SQL reference guide on the W3 Schools website. I’ll also give a few examples to get us started.

Filter An Excel File Where A Column Is Equals A Specific Value

Suppose we wanted to only wanted to extract the employees who live in the US state of New York (abbreviation: NY).

Write this query in the SQL statement field. Notice that we’ve added a WHERE clause to our SQL statement. The WHERE clause allows us to test each row for a specified condition. In this example the us_state column must equal the text NY.

SELECT * 
FROM [Sheet1$]
WHERE [us_state] = 'NY'

After the desktop flow is run we check the QueryResult variable and see it shows only employees from New York (NY).

Filter An Excel File For Values Greater Than A Specific Number

Next, let’s try to only get the employees whose annual salary is over $80,000.

Write this query in the SQL statement field. Notice that we use the greater than symbol to compare the annual_salary field to a number. We also use the ORDER BY clause to sort the query results from the highest salary to the lowest salary.

SELECT * 
FROM [Sheet1$]
WHERE [annual_salary] > 80000
ORDER BY annual_salary desc

When we run the desktop flow our QueryResult variable looks like this.

Filter An Excel File For Values Between Two Dates

This example is one I have come back to many times. We can use a SQL query to return only the results between two dates. In this one I want to get only the employees hired during the year 2020.

Write this code in the SQL statement field. We can use the BETWEEN operator to specific two dates for comparison. Notice how dates must be written within two # symbols.

SELECT * 
FROM [Sheet1$]
WHERE [hire_date] BETWEEN #1/1/2020# AND #12/31/2020# 

Once again, run the desktop flow and open the QueryResult variable and it will only show employees with hire dates in the year 2020.

Filter An Excel File To Return Top Rows

This final examples shows how to get only the top 3 rows in an Excel spreadsheet.

Write this code in the SQL statement field. The SELECT TOP clause can be changed to increase or decrease the number of rows returned.

SELECT TOP 3 *
FROM [Sheet1$]

Run the desktop flow and check the QueryResult variable to confirm only 3 values are returned.

Did You Enjoy This Article? 😺

Subscribe to get new Power Automate For Desktop articles sent to your inbox each week for FREE

Questions?

If you have any questions about Power Automate Desktop: Read & Filter Excel With SQL Query please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion.