Unanswered

Bulk Append of Data (20,000 Rows in one go) from Excel to SharePoint List

(1) Share

Report

Posted on by AkshayManke

Hello All,

As mentioned in the subject line, I am trying to build a flow which will copy all the data from an excel table to a list. The list having below type of columns.

1. Person Field

2. Choice Field

3. Date Field

4. Single Line of Text

The very precise solution I can see is of Paul Murana's this video, How to use Power Automate and the SharePoint Batch API to create thousands of list items quickly (youtube.com)

In the above video he explained very nice way, but I am encountering with some errors which I am not able to figure it out.

I was also thinking to use traditionally ways like below, but each of them is having some limitation.

Direct Upload from Excel - This creates a new List which I don't want.
Connect SP List to Access - Some old references are there but still it is for creating a new list.
Using Power Automate Flow with Apply-To-Each loop - This is failing considering the data volume.

I am sure that there must have some ways to achieve this. Requesting all the expert to please guide me for this.

Many thanks in advance.

Warm Regards,

Akshay

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

ScottShearer 25,290 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

@AkshayManke

Do you have a premium Power Automate license? If not, you'll likely bump into some Flow limits such as a max number of loops (5000)

I'll guarantee that if you use Access, it will time out on you. When I have used access for this, I generaly work in batches of a few hundred to prevent the timeout.

Was this reply helpful? Yes No
AkshayManke 2 on at

Like (0)

Report
Copy link

Link copied!

Hello @ScottShearer,

Thanks for the reply. I have premium license, but I don't prefer that as it will not be a good idea to use apply to each for adding 10000 items as it will have that many number of calls which will result in issues. Also, as you rightly said using Access will timeout the connection this I have experienced because the of people field. When I am adding text, it is converting to username where it is taking time.

Can you please suggest any workaround or way to do the task?

Thanks,

Was this reply helpful? Yes No
AkshayManke 2 on at

Like (0)

Report
Copy link

Link copied!

I got 50% success with the attached flow now and i am able to create records which are having single line of text column types. However now i am looking for adding records with People Picker, Date, Choice type fields. Can anyone please help ?

BatchCreateShareP...

Was this reply helpful? Yes No
takolota1 4,980 Moderator on at

Like (1)

Report
Copy link

Link copied!

@AkshayManke

You'll find people who faced similar issues with getting different column types to work with SP batch processes in this thread: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoint-Lists/td-p/1365410

Was this reply helpful? Yes No
AkshayManke 2 on at

Like (0)

Report
Copy link

Link copied!

Hi @takolota, Thanks for the suggestion. I will have a look in next three days and will get back to you on the result.

Warm Regards,

Was this reply helpful? Yes No