Import Excel Data to SharePoint List with Power Apps and Power Automate

You got Excel data (man, do you have lot's of it) and sometimes you need that data into SharePoint. In one off scenarios, no big deal, you go to SharePoint and upload it to the list and life is good. But, what about when that is a daily challenge? That is where automation comes into play.

In this guide, we will walk through what it would look like if you had a Power Apps app for the user to upload the Excel file to, then the xlsx file will be passed to Power Automate to extract, filter, and then save the rows directly to a SharePoint list. The key here is Power Automate cloud flows and their ability to interact with Excel files for bulk import and SharePoint.

Now, while this will focus on starting at Power Apps, keep in mind that the flow mechanics could be applied to many other scenarios.

• When a file is uploaded to SharePoint

• When you receive the Excel file as an email attachment

• Or even if you had another format like CSV

Alright, let's walk through the process of Import Excel Data to a SharePoint list with Power Apps and Power Automate but, before we do, I have another option. If you want more details and to see all of this in action, check out the video Import Excel Data to SharePoint List with Power Apps and Power Automate

Using Power Apps for the User Upload

The Power Apps app is pretty straightforward. Give the user an Attachment control, when the Excel file is attached, then run a Power Automate cloud flow to do bulk import. Once the flow finishes successfully, then Refresh the SharePoint list so the user can see their data in the app.

Now, if you are not used to the upload method for Power Apps, I have another blog post here: Upload to SharePoint from Power Apps v2. Using the Power Apps attachment control and the Power Apps v2 trigger in Power Automate, the process is pretty straightforward. Because to make this process easiest, the flow will create the file in SharePoint, then read it, then delete it. Seems like extra steps but it is the best path.

Power Automate does the bulk of the work

Now lets look at the flow. We will do it in mini sections to make it easier to follow but, everything below is all in one flow.

Power Apps v2 trigger and SharePoint Create file

The Power Apps v2 trigger for your cloud flow has one input of File type. This allows the app to pass the Excel file to Power Automate.

Then the Create file in SharePoint action needs you to configure the site and the document library you want to store the Excel in. Keep in mind, we are only storing it long enough to process it, we will delete it at the end. Or if you want to keep it, that is great also. Either way we need to store it for a hot minute to do the following steps

For the File Name and File Content, with the new flow designer they are easily added as dynamic content.

Get Tables from Excel

The Excel file must have a table, and in this example only one table. This action is getting the list of Tables from the XLSX file so you can reference it in the following action. The Id is the dynamic content from the Create File step.

But Shane, my file is a CSV or doesn't have a table. Well, then you are going to have to do something about that. For the last customer, they just made it a requirement that someone turn the data into an Excel Table before upload. Other options, could include using an Office Script to do it for you. That is a little trickier but there is a video here to help: Using Excel Scripts with Power Automate to convert CSV

List Rows present in a Table

The first three fields are repeats of above but that Table one, that is a tad tricky because you have to use an Expression.

first(outputs('Get_tables')?['body/value'])?['Id']

That grabs the Id of the first table from the previous action. Back to the concept of, if you are doing a bulk, repeating upload like this, you are going to have to have a planned structure for your Excel. This has never been a problem for a customer but, I wanted to make sure you were aware.

And speaking of customers - What can we do to help you? Just hit the Contact button and let us know. We can help for 30 minutes or 30 months, whatever you need, we got it.

Also, at the bottom check out the DateTime Format. If you are going to import dates make sure you have set the format to ISO 8601, the other serial method is confusing and somehow involves 1899. No thanks.

Filter Array if Necessary

Hopefully you have clean data but I didn't, so I used a Filter Array action to remove all of the unclean rows. Using a formula like:

@and(
not(empty(item()?['Name'])),
not(empty(item()?['Amount'])),
not(empty(item()?['Date'])),
not(empty(item()?['Email']))
)

This returns only the rows that don't have blanks in any of those columns. Why? Because blank fields will make the whole thing fail. Now in my example, I just threw away the bad rows, but really, you should probably tell the user they had bad data.

Loop Through the Rows and Create SharePoint List Items

This Apply to each action is looping through all of the Excel rows from the Filter action. For each row of Excel data it is using the SharePoint Create Item action. This is how you do the bulk import from Excel to a SharePoint list. Nothing fancy at this point.

Now if you go to set this up with the dynamic file and tables you will have challenges with your dynamic content not showing up. It is annoying. In the first video way back at the top I talk you through that challenge.

Long story short, you have to use a static file when configuring the flow. Then once everything is setup you will go back and change things to be dynamic. It is confusing and a big part of the video, so if that is new to you, go watch. Sorry, to much to explain in text.

Delete the File

Flow is annoying, whoops I shouldn't say that, flow is annoying because sometimes it locks files even though it doesn't really have the file. Is that better? Either way, if you want to delete the Excel file you will need this.

You are going to hit the API using the HTTP action as shown above. This will override the file lock that was causing errors. Don't over think it, just use this and move on.

Wrapping up for Import Excel Data to SharePoint List with Power Apps and Power Automate

There you go my friends. You let a user upload a file with a Power Apps app, then you sent it to Power Automate. Power Automate did all of the hard work of saving the file, grabbing all of the Excel rows, importing them to SharePoint, and then deleting the file. Pretty cool!

Remember, if you need help with this or anything else Power Automate just hit that Contact button. We got you!