A Power App that imports data from an Excel file to a SharePoint list (or any data source) can be a game changer for a business. This integration not only streamlines the data entry process but also allows for better data management and organization. By automating the process with Power Automate, businesses can save time and resources while improving the accuracy and reliability of its data. Additionally, the data can be easily accessible and updated from anywhere, making it easier for teams to collaborate and make informed decisions based on real-time data.
In this post we will go through the Shane’s video where he shows you how to use Power Apps to import an Excel file to your SharePoint List or other data source. As always, Shane breaks it down and reveals how to handle a few gotchas because of dates and using dynamic data from the Excel file.
Click the video below to get started!
Click the link below to view more of Shane Young's videos:
Key Timestamps:
0:00 Power Apps import Excel to SharePoint list or any data source
0:59 Demo of the Excel xlsx file import to SharePoint list with a Power Automate flow
2:18 A demo and explanation of the Power Automate flow
8:25 Create the flow, connect it to Power Apps, and Create file in SharePoint
9:48 Excel Online Business Get Tables
11:17 List Rows Present in a Table and the ISO 8601 date format
12:00 Create Item in SharePoint using the dynamic content we imported
14:03 Updating the flow to make the IDs dynamic
14:46 Using the First expression in Power Automate
Copying the Excel file up to SharePoint
The first step in the process is to generate an Excel file and store it in a SharePoint document library. From there we will be able to use flow to open and read the data we want to import into our SharePoint list. Refer to Shane’s video on How to upload files to SharePoint with Power Apps and Automate for a full explanation on uploading files.
Using dynamic content when working with Excel files
When using Excel files in Power Automate, we need to reference the column names when working with the data. However, this can become challenging when trying to build a flow using dynamic content from a step that doesn't involve an actual Excel file. This is because Power Automate cannot determine the available columns without reference to a specific Excel file. For example, if you have an Excel file with the following data in Table1:
Now try building your flow using dynamic content using List rows present in a table. Actions that we use later in the flow will not have the columns under dynamic content.
Next, build the flow directly pointing to our sample Excel file. When we look at the dynamic content, we can see all the column names.
Using this method, we can build the rest of the flow and use the columns names. Remember to go back and change the Excel file refences to use dynamic content once you are finished with the flow.
Working with Dates between Excel and SharePoint
Excel stores data in a different format than SharePoint Lists can understand as a date. In order to make them the same, use advanced options in the List rows present in a table action.
Select Show advanced options in List rows present in a table action
Click to see the selection for DateTime Format
Select ISO 8601
Writing the Excel to the SharePoint List
When writing data to your SharePoint List (or other data sources), Power Automate tries to present only dynamic content that matches the content type that the column can exist. So you may have a SharePoint list column that can only accept numbers, but you won’t be able to select the Excel column from dynamic content. If this happens, just type in the name of the column to search and it will appear.
Deleting the Excel File
Once the data has been written to the SharePoint List, you will want to delete the Excel file you created earlier from SharePoint. This not only prevents unnecessary duplication of the Excel file, but it also allows the Flow to process another Excel file with the same name.
The issue is that SharePoint places a “lock” on a file when it is first created which will prevent you from deleting it right away. Instead of waiting for the lock status to clear on its own, use the following SharePoint Rest API to release the lock as documented from www.cleverworkarounds.com.
_api/web/Lists/GetByTitle('<DocumentLibrary>')/GetItemById(<DocumentID>)/recycle
Conclusion
Incorporating a Power App that imports data from Excel to a SharePoint list (or any data source) with Power Automate improves data management, accuracy, and accessibility. The steps outlined in this blog post provide a guide to help businesses implement this integration. With the power of Power Apps and Power Automate, businesses can now easily streamline their data entry processes and take their data management to the next level.
Links
Additional Learning:
Power Platform University
Are you looking for full, personalized Power Platform training? Do you want a mentor to help guide you on your journey? Do you prefer real world content instead of academic concepts? Then Power Platform University from PowerApps911 is for you.
Learn more here.
If you prefer to learn on your own, there is a downloadable app included with this video in our YouTube Training Library for only $15/month!