Power Automate / Flow - Retrieve Data from a Web Page and Include it in Excel Tutorial

Learn how to use Power Automate Desktop to extract information from a website and transfer it into an Excel spreadsheet. With a step-by-step tutorial, you'll create a flow, customize variable names, and initiate a loop to delete and retrieve web data. Discover how to efficiently automate the process, saving you from multiple manual operations. Watch the tutorial now to streamline your data extraction and management tasks!

04:35
801 views

Available in Microsoft365

Objectifs :

This tutorial aims to teach users how to utilize Power Automate Desktop to extract information from a website and transfer it into an Excel spreadsheet, streamlining data management and reducing manual entry tasks.

Chapitres :

Introduction to Power Automate Desktop
In this tutorial, we will learn how to use Power Automate Desktop to extract information from a website and transfer it into an Excel spreadsheet. This process will help automate data collection, making it more efficient.
Creating a New Flow
To start, create a new flow in Power Automate Desktop. Since the flow is more complex, there are no predefined templates available, so we will begin with a blank canvas. Name your flow and confirm your choice.
Setting Up the Canvas
Once in the canvas space, filter the action column on the left to list the browser actions. The first action is to open Microsoft Edge. Drag and drop this action onto the canvas and enter the URL of the desired platform, choosing to open it in expanded mode (maximized window).
Opening the Excel File
Next, move to the Excel actions to open your target file, which in this example is synchronized with OneDrive. Each action will generate one or more auto-named variables, but it is advisable to customize these variable names for easier flow maintenance and element identification.
Customizing Variable Names
For this tutorial, we will customize only a few variable names by double-clicking on them. This allows for multiple executions of the flow. Make sure to clear the content of the Excel table before injecting updated data into it.
Reading Table Content
Add an action to read the table content by retrieving data from the file and storing it in a variable named 'Excel data'. This will allow for initiating a loop to delete each row. Initialize the loop variable at two to start from the second row and set the loop end at the count of rows in the 'Excel data' variable.
Deleting Rows
For example, if your file contains 50 rows, you will need to create 49 deletion loops, preserving the header row. Add the Excel row deletion action, which will delete row two at each loop, repeating this 49 times.
Extracting Web Data
To retrieve web data, use the 'Extract data from web page' action. Once in the properties window, switch to the previously opened browser page. A Power Automate web assistant should appear when hovering over page elements, with a red box delimiting a detected area.
Selecting Elements to Extract
Right-click and select 'Extract element value' for the desired items. Repeat this action three times for the items to be retrieved, corresponding to the columns of the Excel file (trainer name and details). Power Automate will automatically detect recurring elements on the entire page, saving you from individually clicking on each block.
Defining Navigation and Data Storage
Define the navigation arrow at the bottom of the page as the page selector. Next, set the number of pages to process to capture the first five pages. The data will be stored in a variable named 'data from web page'.
Writing Data to Excel
You can now write this data into your spreadsheet, starting from cell A2 (first column, second row). Add an action to automatically resize the columns based on the data present, and finalize by closing Excel and your browser instance.
Testing the Flow
Save the flow and then test it. You will see on your screen that actions are being performed, and after a few seconds, you can verify that your Excel file is correctly populated.
Customizing Page Extraction
To have the option of choosing the number of pages to extract, initialize an input dialogue box at the beginning of the flow. Customize the title and content in the web extraction action, replacing the value for the number of pages to extract with the one entered in the dialogue box.
Conclusion
By following these steps, you can efficiently extract information from your website and transfer it into an Excel spreadsheet, saving you from numerous manual copy-paste operations. Test the flow again to ensure everything works correctly.

FAQ :

What is Power Automate Desktop used for?

Power Automate Desktop is used to automate repetitive tasks and workflows on your computer, allowing you to save time and reduce manual effort.

How do I create a flow in Power Automate Desktop?

To create a flow, open Power Automate Desktop, start with a blank canvas, name your flow, and then add actions from the action column to build your workflow.

Can I customize variable names in Power Automate?

Yes, it is advisable to customize variable names for easier flow maintenance and element identification. You can do this by double-clicking on the variable names.

How do I extract data from a website using Power Automate?

You can extract data by using the 'Extract Data from Web Page' action, selecting the elements you want to retrieve, and defining the navigation for multiple pages if needed.

What should I do if my Excel file is not updating correctly?

Ensure that you clear the content of the Excel table before injecting updated data and check that your flow is correctly configured to read and write data.

Is it possible to choose the number of pages to extract data from?

Yes, you can initialize an input dialogue box at the beginning of the flow to allow users to specify the number of pages to extract.

Quelques cas d'usages :

Automating Data Entry from Web to Excel

A marketing team can use Power Automate Desktop to extract customer data from a website and automatically populate an Excel spreadsheet, saving time on manual data entry.

Monthly Reporting Automation

An analyst can set up a flow to extract sales data from a web portal and update an Excel report monthly, ensuring that the report is always up-to-date without manual intervention.

Data Cleanup and Management

A data manager can automate the process of cleaning up an Excel file by deleting unnecessary rows and updating it with fresh data from a website, improving data accuracy and efficiency.

Web Scraping for Market Research

A researcher can use Power Automate to scrape data from multiple web pages for market analysis, storing the results in Excel for further analysis and reporting.

Glossaire :

Power Automate Desktop

A Microsoft application that allows users to automate repetitive tasks and workflows on their desktop by creating flows.

Flow

A series of automated actions that are executed in a specific order to accomplish a task in Power Automate.

Variable

A storage location in programming that holds data that can be changed during the execution of a flow.

Excel Actions

Specific commands in Power Automate that allow users to interact with Excel files, such as opening, reading, and writing data.

Loop

A programming construct that repeats a block of code a certain number of times or until a condition is met.

Extract Data from Web Page

An action in Power Automate that allows users to retrieve specific information from a web page.

Input Dialogue Box

A user interface element that prompts the user to enter information, which can then be used in the flow.

Synchronized OneDrive

A cloud storage service from Microsoft that allows users to store files and access them from multiple devices.

00:00:03

we will learn how to use power automate desktop to extract

00:00:07

information from a website and transfer it into an Excel spreadsheet

00:00:13

to start create a new flow in power automate desktop.

00:00:16

Since the flow is more complex, there are no predefined templates.

00:00:20

So we will begin with a blank canvas

00:00:23

name your flow and confirm

00:00:27

once in the canvas space filter,

00:00:29

the action column on the left and list the browser actions.

00:00:33

The first action is to open Microsoft edge in our case,

00:00:37

dragon drop the action and enter the URL of the desired platform by

00:00:41

choosing to open it in expanded mode equivalent to a maximized window.

00:00:46

Next move to the Excel actions to open your target file

00:00:52

which in this example is on the synchronized onedrive.

00:00:56

Each action will generate one or more auto named variables.

00:01:00

It is advisable to customize the variable names

00:01:03

for easier flow maintenance and element identification.

00:01:07

For this tutorial,

00:01:08

we will customize only a few variable names by double clicking on them

00:01:12

to allow multiple executions of the flow.

00:01:14

Make sure to clear the content of the

00:01:16

Excel table before injecting updated data into it.

00:01:20

Add an action to read the table content by retrieving data from

00:01:24

the file and storing it in a variable named Excel data.

00:01:28

This will allow for initiating a loop to delete each row,

00:01:33

initialize the loop variable at two to start from the second row

00:01:37

and set the loop end at the count of rows in the Excel data variable.

00:01:42

For example, if your file contains 50 rows,

00:01:45

you will need to create 49 deletion loops.

00:01:48

The header row being preserved,

00:01:51

add the Excel row,

00:01:52

deletion action which will delete row two at each loop and do this 49 times.

00:01:58

If your file contains 50 rows

00:02:02

to retrieve web data, use the extract data from web page action.

00:02:08

Once in the properties window switch to the

00:02:11

previously opened browser page on the web URL

00:02:14

A power automate web assistant should appear

00:02:17

when hovering over page elements.

00:02:19

A red box delimiting a detected area will appear right, click and select

00:02:24

extract element value. Then text

00:02:28

repeat this action three times on the items to be retrieved corresponding

00:02:32

to the columns of the Excel file trainer name and details.

00:02:39

Power automate will automatically detect recurring elements on the entire

00:02:43

page saving you from individually clicking on each block.

00:02:47

This is also visible in the power automate preview.

00:02:51

Then

00:02:52

define the navigation arrow at the bottom of the page as the page selector

00:02:59

next set the number of pages to process to capture the first five pages.

00:03:04

The data will be stored in a variable named data from web page.

00:03:10

You can now write this data into your spreadsheet.

00:03:17

Starting from cell A two first column, second row,

00:03:22

add an action to automatically resize the columns based on the data present,

00:03:30

finalized by closing Excel

00:03:35

and your browser instance.

00:03:40

Save the flow, then test it.

00:03:45

You will see on your screen that actions are being performed.

00:03:50

And after a few seconds, you can verify that your Excel file is correctly populated.

00:03:57

To have the option of choosing the number of pages to extract,

00:04:00

initialize an input dialogue box.

00:04:02

At the beginning of the flow,

00:04:05

customize the title and content

00:04:09

in the web extraction action,

00:04:10

replace the value for the number of pages to

00:04:13

extract with the one entered in the dialogue box.

00:04:17

Test the flow again to ensure everything works correctly.

00:04:21

By following these steps,

00:04:22

you can efficiently extract information from your website

00:04:25

and transfer it into an Excel spreadsheet,

00:04:28

saving you from numerous manual copy paste operations.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

508 Views

Microsoft Video Training Power Automate – From discovery to mastering automation

Boost your productivity with our Power Automate course! Learn to automate your tasks and seamlessly integrate various Microsoft 365 services...

ALL COURSES