How to Copy Excel Records to SharePoint List via Power Automate

We can’t avoid circumstances where we need to add items on the list dynamically. Most of the time, we get the data from excel especially, when these are from other apps. So to lessen the time rendered in adding it manually, it is best to add it dynamically using Microsoft Power Automate Flow. So this blog will be all about copying excel records to a list.

Preparing the excel file

To start, ready the excel file. The file should contain an organized data to add the items efficiently.

Excel Records

Add the items to a table. Then label the columns well and make sure the items listed are what you expected them to be. The above image is an example of the correct way on how the record should look. After preparing the excel file, upload it to a document library. We want direct access to the file, so we need it inside a document library.

Upload excel file to a document library

Building the flow

The excel record is ready, so we can now start building our flow. First, let’s create a manually triggered flow. Note: You can always choose the trigger that you want based on the scenario that you are on.

Add the action List rows present in a table. This action will get all the records inside the excel table. Add the Location of the document library where you uploaded the excel file.

Excel Records to List

Then select the document library and the file. For the Table field, select Table 1 since we’re only using one table. Note: ‘List rows present in a table‘ action only gets the first 256 items of the table. If you have more than 256 items on your table, you can use the Skip Count field to skip the items that you already have copied to the list.

Skip Count

Next, use the Apply to each action to iterate the items retrieved from the last flow action we added.

Iterate excel record

There are times that the Date field is not retrieved correctly.

Incorrect Date format retrieved

For this one, we’ll need to convert the retrieved data to the correct format. Use this formula to convert the date:

 addDays('1899-12-31',int(items('Apply_to_each')?['Date Hired']))

I added a Compose action to have a temporary holder of the value.

Compose convert date format

Then add the items to the list using the Create Item action. Add the Site Address and the List Name. Also, enter the values to the fields you want to fill out. For the Date field, add the Output of the Compose action we set up.

Create the items from the excel file


The flow is now ready. Run the flow to start adding the items to the list. Below is the example record that I copied to the list.

Copied items from the excel file

That’s it for the copying of excel records to a list. If you have any questions about the topic, please let me know at the comment box below or email us directly. Have a nice day!

Accounting.js Automation Collaboration Competitors Connect Content Type Design Expand Flows Hillbilly Tabs Issues Javascript Limitation Limitations Microsoft Teams ModernScriptEditor NodeJs Node Versioning Numeral.js O365 Office 365 OneDrive Out Of The Box PnP Power Automate PowerShell Pwermissions Rest Endpoint ScriptEditor Send an HTTP Request to SharePoint SharePoint SharePoint Architecture SharePoint Designs SharePoint Modern SharePoint Online SharePoint Tabs ShellScript SPFX SPO Sync Teams Transform JS TypeScript Versioning Workflows

Leave a Comment

Your email address will not be published. Required fields are marked *