Unanswered

Syncing a Share Point list with a table from an excell source file in Sharepoint Directory

(1) Share

Report

Posted on by CW-11051221-0

Anyone have a better way to sync data to a large SP list? about 3k rows with 70 columns, needing to update milestone date columns on a daily or weekly schedule. Previous flow attempts have proved to be too slow and never succeed.

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Assisted by AI

MarkRahn 1,418 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @CW-11051221-0

I had to actually do this in the past with several thousand (20k) rows of data from an Excel Spreadsheet that was uploaded daily to a SharePoint Document Library. It worked well and went from taking almost all day to run to taking about 15 to 30 minutes.

Take a look at this link:

Batch Update, Create, and Upsert SharePoint Lists by @takolota1

The flow I made loaded the data from Excel and from SharePoint. It then did some XML comparisons using XPath to find changes, new items, and deletions. It then processed the items using the SharePoint REST API in Batches.

I asked Copilot to come up with the basic outline:

Steps to Compare Excel vs SharePoint Using XML + Process Changes via REST Batch API

Load Excel data
Use List rows present in a table and reshape the output into a simple XML structure (<root><row>…</row></root>).

Load SharePoint list data
Use Get items (with pagination) and convert the results into the same XML structure as Excel.

Use XPath to find NEW items
Select rows that exist in Excel but not in SharePoint based on the key column.

Use XPath to find DELETED items
Select rows that exist in SharePoint but not in Excel.

Use XPath to find CHANGED items
Compare Excel and SharePoint rows with the same key and detect differences in one or more fields.

Chunk each result set into batches of 100
SharePoint REST API batch requests support up to 100 operations per batch, so split the XML nodes accordingly.

Build REST API batch payloads
Create the $batch envelope containing the individual operations:

POST for creates

MERGE for updates

DELETE for deletions

Send batch requests to SharePoint
Use Send an HTTP request to SharePoint with POST /_api/$batch and the multipart/mixed payload.

(Optional) Enable parallelism
Run multiple batch groups at once to speed up processing for large datasets.

Log results
Track how many items were created, updated, or deleted for easier monitoring and troubleshooting.

I hope this helps you.

This community is supported by individuals freely devoting their time to answer questions and provide support. They do it to let you know you are not alone. This is a community.

If someone has been able to answer your questions or solve your problem, please click Does this answer your question. This will help others who have the same question find a solution quickly via the forum search.

If someone was able to provide you with more information that moved you closer to a solution, throw them a Like. It might make their day. 😊

Thanks
-Mark

Was this reply helpful? Yes No
CW-11051221-0 12 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Mark,

Thank you for the information. I am using your flow but hitting a snag with the below error at the GenerateSPData. Any help would be greatly appreciated! Also the inputs for this step appear to have generated accurate meta data, but the output is all nulls.

The action 'GenerateSPData' was executed for '2669' iterations resulting in an aggregated result size that exceeded the maximum value '209715200' bytes allowed. Please reduce the number of iterations to ensure that the aggregated results size is less than '209715200' bytes.

Was this reply helpful? Yes No
CW-11051221-0 12 on at

Like
a
(0)

Report
Copy link

Link copied!

I should add that my data sets are 2k to 5k in rows but ~70 columns that i want updated weekly.

Was this reply helpful? Yes No
David_MA 14,956 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

If you need a way to sync large amounts of data within Microsoft 365, I recommend investing in Layer2 Cloud Connector. We have been using it for a number of years now and it works great. Depending on the number of changes in each sync, 20,000 rows could sync in a matter of a few minutes (smaller data sets will sync in seconds). You can get more information at Layer2 Cloud Connector ► integrate and sync data. I believe you'll need the enterprise version to have the connector that includes Excel.

Do all 70 columns need to sync? If not, Layer2 will allow you to only sync the data needed provided the two data sources have a common primary key. I sync SQL database tables to SharePoint for lookup columns. Often, the SQL table will have more columns than I need to sync to SharePoint. I can thus choose only the required columns, and it speeds up the sync process greatly.

Was this reply helpful? Yes No
CW-11051221-0 12 on at

Like
a
(0)

Report
Copy link

Link copied!

Not every column needs to sync but there are 40-50 Date columns to update.

Was this reply helpful? Yes No