Power Automate Read Excel File From SharePoint

In this article, I will explain how to work with Power Automate read excel file from SharePoint, and how to import excel data into an existing SharePoint list using Power Automate with various examples.

Also, we will see how to update SharePoint list from Excel using Power Automate, and Power Automate get excel file from SharePoint.

Power Automate Get Excel File from SharePoint

To get the Excel file from the SharePoint document library, follow the below points:

Example:

I have a SharePoint library with files and folders, as you can see from the image below:

In the SharePoint library represented below, I have created an Excel file [Task List], where I wanted to get those details and send an email with all the details.

Follow the steps below:

1. Browse make.powerautomate.com to open the Power Automate Home page -> click on +Create and select Instant cloud flow -> Choose Manually trigger a flow and tap on the Create button.

2. After that, add the Get file content action and configure the required parameters:

• Site Address: Choose the SharePoint site or enter the custom value.
• File Identifier: Select the file you want to get details from the open folder icon.

3. Then, add List rows present in a table flow action. Set the required details:

• Location: Select the file location where it is present from drop-down.
• Document Library: Choose the document library where the file is present.
• File: Select the file from the Open folder icon.
• Table: Choose the table from the drop-down.

4. Take a Create HTML table flow action to store Excel retrieved data in a table.

• From: Take Body/Value under dynamic content of List rows present in a table.
• Columns: Choose the Custom column.

5. To represent all the details retrieved from an Excel sheet, take the Send an email(v2) flow action.

• To: Provide an email address to whom the mail should sent.
• Subject: Provide subject line for a mail.
• Body: Format the body along with output.

6. Now, click on save and test the flow. Select Manually and click Runflow.

7. When the flow runs successfully, it will send an email to the with all the details as shown below screenshot:

This is how to get an Excel file from SharePoint using Power Automate.

Import Excel Data into an Existing SharePoint List using Power Automate

To import the data from the Excel spreadsheet on the SharePoint list using Power Automate, see the example below.

Example:

I need to import Excel data into the SharePoint list. The screenshot below displays the Excel sheet [Employee Details].

Format the Excel sheet as a table, as it looks like the image below.

After that, create a SharePoint list with the column names, as you can see from the screenshot below:

Go through the steps below:

1. Create a Power Automate Instant cloud flow.

2. Select and choose the List rows present in a table action. Provide the required parameters:

• Location – Choose the SharePoint site where the Excel file is present.
• Document library – Select the document library where the Excel file exists.
• File – Select the file from which data needs to be extracted.
• Table – choose the table name that you have created in the Excel sheet.

3. Next, add the Create item flow action. Configure the parameters:

• Site Address: Select the SharePoint site
• List Name: Choose the created list name from the dropdown

Then, For each loop will be added automatically, by taking the Body/value from List rows present in a table.

Click Show all to display all the parameters.

• Employee Id: Take Employee id from dynamic content.
• Employee Full Name: Use the below expression.

concat(items('Apply_to_each')?['Employee First Name'], ' ',items('Apply_to_each')?['Employee Last Nmae'])

For the Employee Date of Birth date field, use the expression below:

By default, the Excel date column returns a random integer value. To overcome this, we are using the expression below to display the date in yyyy-MM-dd format.

addDays('1899-12-30',int(items('Apply_to_each')?['Date of Birth']),'yyyy-MM-dd')

• Donor Email Address, Gender Value, Designation Value: Take the values from dynamic content.

4. Next, save and run the flow by selecting the run flow option. Once the flow runs successfully, like the below:

5. You can see all the Excel data has been imported from the Excel file to the SharePoint list.

This way, we can import data from an Excel spreadsheet to a SharePoint list using Power Automate.

Also, you may like:

• Power Automate Rename SharePoint File
• Power Automate Split String into Array
• Power Automate Send Email With Attachment
• SharePoint Rest API Crud Operations
• Delete Files Older than 3 Years from SharePoint Document Library Using Power Automate
• Post an Adaptive Card to Microsoft Teams Using Power Automate

Conclusion

I hope you found this article helpful. In this article, I covered how to import excel data to SharePoint list using Power Automate, how to update SharePoint list from excel using Power Automate, and many more like:

• Excel to SharePoint list power automate
• Power Automate get data from excel
• Power Automate get excel file from SharePoint

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.