Fetch Column Headers from Excel Files in a Folder using Power Automate

This article mainly focuses on extracting the column header names from multiple Excel sheets stored in a folder using Power Automate.

Here’s a step-by-step process for creating a Power Automate for fetching the data from Excel sheets in a folder.

1. Create an Instant Cloud Flow

Start by creating a new instant cloud flow in Power Automate.

2. Add a Manual Trigger

Choose a manual trigger to run the flow on demand.

3. Initialize a Variable

Create an array variable (e.g., Keys) to store extracted column names along with their respective Excel sheet names.

Initialize a Variable

4. List the Excel Files in the Folder

Use the List folder action.
Provide the Site Address and Folder Path where the Excel files are stored.
This step retrieves all Excel file names within the folder.

5. Loop Through Each File

Add a For each control.
Select the output of the List folder action so that the flow processes each file individually.

6. List Rows in the Excel Sheet

7. Extract Only the First Row (Headers)

Add a Compose action to fetch the first row (which usually contains the column headers).

Extract Only the First Row (Headers)

8. Apply to Each (Loop Through the Headers)

Add another Apply to each action.
In the Select an output field, use an XPath expression to get header names from the first row.

Apply to Each (Loop Through the Headers)

9. Append Column Names with File Name

Use the Append to array variable action.
Concatenate the Excel sheet name with each header name, replacing any special characters.

Append Column Names with File Name

The final Power Automate flow:

Power Automate flow