Greetings
Hello everyone! I apologize for my prolonged absence; I’ve been incredibly busy with a mountain of work (I guess Power Platform is just that popular, haha!).
Problem
As you all may be aware, Power Automate allows us to effortlessly import large amounts of data into SharePoint using a simple flow like this.
While this flow is practical and effective for small datasets (100-1,000 records), it can indeed become a problem when dealing with larger datasets (10,000+ records). Let me explain why.
- It’s extremely slow. You may need a days to finish the import.
- It’s consume large amount of Power Platform request. each action of the flow are counted in Power Platform request, once you hit the limit, you get throttled.
No need to worry, as I have a solution for all of you!
Solution
Before we dive in, I want to give full credit to Paulie for writing this superb blog post. I’ve followed his lead and made some improvements to make it more flexible and easier to use.
I have do all the work for you, all you need to do is
1. import my Solutions (Download link below) to your desired Power Platform environment.
2. Prepare your Excel (.xlsx) file, your xlsx file must meet these requirements:
- Data is in defined Table, non-table Workbook is not supported.
- Your workbook must contain only one table.
- Column name must exactly match with your SharePoint internal column name.
3. Navigate to Power Automate website, you will see this flow. Run it.
4. Update & fill required data:
- File Content – upload your .xlsx file.
- Clear data – Enable this only when you want to delete all existing data in SharePoint list before the import (Use with care!).
- Pagination – Max: 5000, recommend: 5000.
- SiteURL – URL of your SharePoint Site with slash at the end. (ex. https://yourtenant.sharepoint.com/sites/SiteURL/).
- ListGUID – GUID of your SharePoint list (How to find GUID? >> HERE).
- Click Run
That’s all, you can sit, take some coffee and wait the data to be imported.
How fast is it?
Well, I have tested with the Excel file as you see above.
- The dataset contains 100,000 records.
- The import process takes approximately 90 minutes to complete.
- This is significantly faster compared to the simple flow.
- The import only consumes around 300 Power Platform requests, which is a relatively low number.
Download
You can download Solutions package HERE.
How to import the Solutions? Please refer to my previous blog post.
How it works?
In summary, this flow utilizes the SharePoint API and performs batch operations, allowing multiple records to be sent to SharePoint within a single request, rather than sending one record per request. This significantly improves the efficiency of the data transfer process.
For more detail, in-depth explanation, please refer to this blog post.
