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

Output


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!

AAD TenantId AAD UserId Add-SPOTheme Apply Theme Check Current User Is External User Content Type contentTypeHub CopyExcelRecordToList Create List Item Create list item using rest Current User Permission Custom Theme ExcelPowerAutomate External Users Flows isExternalGuestUser Library Views List Templates List Views Microsoft Flow MS Flow O365 PageContext PnP PowerAutomate Power Automate Power Platform PowerShell React Rest API Rest Endpoint Send an HTTP Request Send an HTTP Request to SharePoint SharePoint SharePoint Color Theme SharePointList SharePoint Modern SharePoint Online SharePoint OOTB SharePoint PageContext Site Page Site Page Content Type SPFX SPO UserIsExternal

Leave a Comment

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