How to Import a Large Excel Dataset into SharePoint Using Power Automate

In this video, I review the steps taken on how I imported 100,000 records of data in Excel to a SharePoint Online List and discuss the challenges to import large datasets into SharePoint Online.
Visit the link below to see the step-by-step breakdown:
www.tw-waytek....
Be sure to subscribe to get all the latest technology tips and to visit my website where you can find the documented version of my videos.
--------------------------------------------------------
CONNECT WITH ME:
My Website: www.tw-waytek.com
___________________________________

[@AVielot]

This was EXACTLY what I needed. Easy-peasy low coding and very effective! Thanks so very much for sharing!😊

[@tw-waytek]

Hello @AVielot , thanks for your positive feedback and I'm glad the video was helpful. Reach out anytime if you have questions or feedback as both are always welcome.

[@assiabouhadouza1927]

Hi, thanks for the video. Why didn't use th import option from the List app directly? Is it because of the size? Thanks``

[@tw-waytek]

Hello @assiabouhadouza1927, great question, the import will timeout with an error message stating the number of rows in the table cannot exceed 20,000 rows. Thanks for reaching out, let me know if you have any other questions.

[@nike33ws]

Am I missing something? There's a 5,000 row threshold limit on Pagination in Power Automate. How are you able to get it to save the Flow with 100,000?

[@tw-waytek]

Hello @nike33ws, thanks for reaching out, under the settings for the "List Rows Present in a Table" action, turn on "Pagination" and enter the "Threshold" of 100,000. This is the maximum pagination limit.

[@ostaydesuarez9414]

The same thing happens to me, and I think they sell the answer.

[@tw-waytek]

Hello @ostaydesuarez9414, thanks for reaching out, there is no conspiracy here, if you go to the link below, do Alt-F and type in "Paginated items" you will see the maximum number of items is 100,000. learn.microsoft.com/en-us/power-automate/limits-and-config Visit the link below for more information on how to do the flow: www.tw-waytek.com/how-to-import-a-large-excel-dataset-to-sharepoint-using-power-automate/

[@marjorieherrera3031]

I get this error for a specific date field "Input parameter 'item/field_13' is required to be of type 'String/date-time'. The runtime value '"45482"' to be converted doesn't have the expected format 'String/date-time'." I've already formatted it as date in the excel table and it still doesn't work. The process works if I put the date fields as "single line of text" but if I try to convert that to date in the List afterward, then it shows blanks. Any idea of what could be happening? And thank you for the video! I didn't know this was a possibility.

[@tw-waytek]

Hello @marjorieherrera3031, thanks for reaching out, this is a good question as I didn't have any date fields in my file or SP List, if you share a screen shot of date formatting in your Excel file and SP List at the link below, I'll try and troubleshoot it for you. I'm glad you like the video, thanks for your positive feedback. www.tw-waytek.com/contact-me/

[@CraigManning-j2h]

In the Excel List rows select the date format as ISO...

[@tw-waytek]

Thanks for sharing @CraigManning-j2h, @marjorieherrera3031 can you confirm if this resolved your issue?

[@UmairKhan-gx4mw]

Hi, great video! I have a query. How are you not getting the threshold error? As soon as I uploaded the Excel to list I am only able to view the data in online sharepoint and not the list desktop app. Also in the settings of list it is showing threshold issue. How do I fix that?

[@tw-waytek]

Hello @UmairKhan-gx4mw, thanks for reaching out, how many rows are you trying to import from Excel and did you update the Pagination settings in Power Automate under the List Rows Present in a Table action? You can also reference the link to my blog under Large Dataset Limitations: www.tw-waytek.com/how-to-import-a-large-excel-dataset-to-sharepoint-using-power-automate/

[@pravinshingadia7337]

Brilliant video - are you doing more?

[@tw-waytek]

Hello @pravinshingadia7337, thanks for your positive feedback, I'll be doing more, do you have anything you'd like to see?

[@blessingjunior2780]

Very helpful

[@tw-waytek]

Thanks for reaching out @blessingjunior2780, I'm happy to have been helpful.

[@nayanfrancis477]

thanks, helpted a lot. Btw My runtime for 22000 records was 17 min, still aboslutly fine

[@tw-waytek]

Hello @nayanfrancis477, thanks for reaching, I'm glad I was able to help and that your import was successful. 17 mins for 22k records is actually not too bad seeing my 50k records was 5 hours, but I guess the column count also affects the import time. Thanks for sharing!!

[@jakubholasek8290]

Inefficient. Use batch processing for sharepoint lists instead.

[@tw-waytek]

Hello @jakubholasek8290, thanks for your feedback, if you have an example you can share (like to another video) that would be helpful.