In this blog post, I discuss how to import 100,000 rows and 56 columns (5.6m data points) into a SharePoint List (Microsoft Lists) from a Microsoft Excel csv file using Power Automate. I downloaded a 1.8 GB csv dataset from Kaggle with 495,000 rows and 89 columns and transformed the data into two 50 MB Excel csv files of 50,000 rows and 56 columns each. The dataset is based on Airbnb listings.
Large Dataset Limitations:
There are a few of limitations to overcome with this import. There is a 256-row base limit when using the Power Automate action “List Rows Present in a Table” in Excel, there is a 20,000 row and 300 columns import limit from Excel data into SharePoint Lists, and there is a 250 mb Excel file size limit to import in SharePoint Lists. Below I discuss how I overcame these limitations using Power Automate.
The flow is initiated by a manual trigger. The first action is the Microsoft Excel “List Rows Present in a Table” action. No ODATA filter query is needed as the data was already transformed. Next provide the location of the document library, the folder in the document library, the file path, then the table name within the file.
To override the 256 row limit, go to settings, turn on Pagination and provide the number of rows you require.
The next action is “Apply to Each” and use the Excel value as the output for each field. Then select the SharePoint “Create Item” action and provide the site address and name of the list. This should expand out the entire number of fields in your list. In my case 56 columns. Then match each field in the SharePoint List with the corresponding column in the Excel table using the dynamic content selector as shown below.
Below are the images of the entire 56 column list and their corresponding columns in the Excel table.
Since I’m only working with two files it was easier to just duplicate the first flow. So, after creating the first flow, I created a second flow using the exact same setup for the other 50,000 row excel file.
The first flow ran for 5 hours 20 minutes, and the second flow ran for 6 hours 30 minutes. Both flows failed but all 100,000 records were imported without issue. I wasn’t able to find any reason for the failure so if you have any insights, please provide them in the comments below.
The flows didn’t take long to build aside from matching the columns, most of my research was spent trying to get around the limitations in SharePoint, Excel and Power Automate. Since time was not a factor, I did not focus my efforts on reducing the duration of the flow, let me know if that is something you’d be interested in learning more about. Overall, it was not as hard as I thought it would be.
