Import Excel data into an existing SharePoint List using Power Automate

Have you ever needed to import Excel data into an existing SharePoint List? Creating a new list and importing data is easy enough using the Create a list based on a spreadsheet feature but adding data to an existing List is not that straight forward.

This blog post will show how I used Power Automate to import Excel data into an existing SharePoint List.

To start, ensure that your Excel data is formatted as a table and the column names and data types align to the destination List. Make sure your Excel file is stored online (i.e. OneDrive or SharePoint).

Create a new Flow using the “Manually trigger a flow” trigger.

Add an “Excel Online (Business)” – “List rows present in a table” action. Set the “Location”, “Documents”, “File” and “Table” values to the location and names that you set in the previous section.

This next section is targeted for date data. Excel dates don’t carry over the way you would expect. We’ll use a variable to store the date from Excel and then apply an expression to do the conversion. You will need a variable per date column for import. Add a “Initialize variable” action, set the name to “varDate1” and type to “String”. I am importing three dates into my existing list.

Add an “Apply to each” action using the “value” output from the Excel “List rows present in a table” action.

Add “Set variable” actions for each of your date columns and set the value to the Excel date columns.

Add a “SharePoint” – “Create item” task, set it your target site and list name. Set the columns to align with your Excel data. For the date columns add the following expression:

  • addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)

Important, if your date data has the time included it must be removed. Add “First” and “Split” functions to keep the date only portion. Example expression:

  • addDays(‘1899-12-30’,int(first(split(variables(‘varDate1’), ‘.’))),’yyyy-MM-dd’)

My complete Flow looks like this:

Save and test your Flow. The data in my Excel listing successfully imports into my existing List.

I hope this helps and thanks for stopping by.

Norm

2 thoughts on “Import Excel data into an existing SharePoint List using Power Automate

  1. Great post Norm. Having done this a number of times in the past I’ve run into a constant issue that comes up. Power Automate / SharePoint do not like to share a file with others and, flows will often fail. I generally end up as a first step taking a copy of the workbook and placing it somewhere that I know has no collaboration issues. At end of run when successfully, delete the workbook ready for the next run.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s