Excel to Excel Large Data Update

[StevenMcElyea]

Hello!

 Over the last year I have been able to learn so much from building flows! Normally, I have been able to figure out my issues, however, this time I need help.

VISION/PLAN: Take a large raw excel file(1,000+ rows, 100 columns, and not all cells have values) exported from a parent system, place it in a document a library, transfer data to a static Excel file(master file), then update MS lists. 

WHAT I HAVE DONE: Built a 3 phase flow

Phase 1) When the raw Excel file is placed in the document library, a flow is built to convert the range of values into a table. Once converted, the file is placed in another document library to trigger phase 2.

Phase 2) Compare data from raw excel to master file using a filter array to update/add rows from raw data to master file. The master file takes the data and ensures the all formatting is set so there are no issues when transferring data to MS Lists. The raw excel file is then moved into its final resting place in a 3rd library, to trigger the final phase.

Phase 3) Take data From master file and update MS lists accordingly. The raw data is then archived.

Issues: all phases have been successfully built and the information flowed just as expected, however, when I try to transfer data in Phase 2 the flow slows down considerably after about 800 rows. My pagination is set to 5000 and concurrency is set to 50 on the 'Apply to Each' portion (add/update rows) my retry policy is set to default. I would also occasionally get a 429 error, so I added a delay of 30 seconds  within the 'Apply to Each'. 

I've even brought concurrency down to 30 and still it is taking way longer than it needs to. 

Does anyone have any tips on how i can improve my flow in phase 2? Make it more efficient.. (note, I do not the office script option, so I can't run that) 

[lbendlin]

Have you considered using a database instead? It would dramatically improve the performance.

[StevenMcElyea]

Hello Ibendlin,

That is essentially what this will be. 

[lbendlin]

I meant a real database like SQL Server.

[StevenMcElyea]

Unfortunately our company doesn't use SQL, so that is not possible. 

[VictorIvanidze]

I believe you can significantly improve performance using MS GRAPH:

https://learn.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0

[StevenMcElyea]

Hello Victor,

Thank you for your reply, after reading that article, it stated that I would need the excel file to be an .xml and not a .xls file. All of my excel files are .xls. will switching over to .xml disrupt data that is currently in there?

Thanks!

[VictorIvanidze]

I don't know. Try to do the same things as shown here: https://blog.udemy.com/excel-to-xml/

[lbendlin]

If your files are .xls then you are pretty much doomed. That format was deprecated in 2003. It is not conducive for automation 

[David_MA]

Unfortunately, I cannot offer advice to solve your issue, I can just tell you how I do what you need to do. First, I would try creating a dataflow to import your Excel data into a Dataverse table. Then to sync the Dataverse table with your SharePoint list, I would use Layer2 Cloud Connector: How to sync Microsoft Dataverse Data with SharePoint. A Step-by-step intro. (layer2solutions.com)

Depending on what you are doing with the data, maybe just having it in Dataverse will be enough for what you need to do as you can access Dataverse tables in all of the Power Platform.

[takolota]

@StevenMcElyea 

Have you considered using batch processing?

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...