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:
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.