Power Automate  

Power Automate Desktop: Read Excel and Use as Data Table (PAD)

Working with Excel files is quite common in our automation workflows. Whether it’s employee records, sales reports, or a simple task tracker, Excel remains a go-to tool for storing data. In this blog, I’ll walk you through how you can read a local Excel file and convert it into a Data Table using Power Automate Desktop (PAD).

Prerequisites

Before we proceed, ensure the following items are ready.

  • Power Automate Desktop is installed.
  • Excel is installed on your machine.
  • A sample Excel file (you can create a simple one with some dummy rows and columns).

Scenario

Let’s say you have an Excel file like this saved locally.

"C:\Users\gowth\Downloads\RPA_Challenge_Sample.xlsx"

Excel

Our goal is to read this file and convert the data into a PAD Data Table variable, allowing us to use it in loops, validations, or other logical operations.

Step-by-Step Guide

Step 1. Launch Power Automate Desktop and Create a New Flow.

Open PAD and click + New Flow. Give it a name like ReadExcelToDataTable.

Step 2. Use the “Launch Excel” Action.

Drag the Launch Excel action onto the workspace.

Set Document path.

Document path

Step 3. Use the “Read from Excel Worksheet” Action.

Now, drag the Read from Excel Worksheet action.

  • Set Retrieve to Values from a range of cells.
  • Range: Leave blank to detect a used range automatically.
  • Output to: ExcelData.
    Excel data

This will extract your worksheet data into a list of rows and columns.

How to Show Excel Data in a Message Box in PAD?

Let’s say you’ve already read your Excel file and converted it into a Data Table called ExcelData.

Here’s how you can display each row in a message box.

Steps of Loop and Show Data

Step 1. Use “For Each” Loop.

  • Action: For Each
  • Value to iterate: ExcelData
  • Current Item: CurrentRow
    CurrentRow

Step 2. Inside the Loop: Use “Display Message” Action.

  • Title: Employee Info
  • Message
    • EmpID: %CurrentRow["EmpID"]%
    • Name: %CurrentRow["Name"]%
    • Dept: %CurrentRow["Department"]%

Make sure the column names match precisely with your Excel headers. They are case-sensitive.

Output

You’ll get one message box per row, like.

Message

Step 3. Close the Excel File (Optional but recommended).

Use the Close Excel action at the end to safely close the file.

  • Save document: No, unless you made any changes.
  • Instance: Use the Excel instance from step 2.
    Excel File

Pro Tips

  • Ensure the Excel file is not already open before PAD attempts to access it.
  • Keep column headers clean and consistent (avoid merged cells).
  • You can also handle empty rows or columns using If conditions inside loops.

Conclusion

Reading Excel files and converting them to Data Tables in PAD is super handy and beginner-friendly. It enables you to integrate traditional office data with automated logic, eliminating the need for coding.

If you're planning to work on automation involving data processing, this trick is a must-have in your toolbox.

Next Steps

  • Try exporting this data to CSV.
  • Use conditions inside loops to filter data.
  • Combine this with email automation to send reports.

Hope you found this article helpful! Please like, share, and comment below if you would like a blog on exporting PAD DataTables to Excel or CSV next time.

Happy Automating!

Membership not found