Read Excel 5X Faster - Power Automate

[@tylerkolota]

There is also an alternate Office Scripts method to get a lot of Excel rows. It is more efficient & more flexible than the video’s method & can even get data not formatted as a table. community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d

[@dineshtalekar8980]

But we have size limitation to pass on to office script

[@tylerkolota]

@@dineshtalekar8980 I updated the forum post to read "Also for V1 the table of desired data is less than the max script output size limit of a few MB or about 300,000 cells (keep reading to get to V1b batching that overcomes the data limit)." to avoid people only reading the 1st section & stopping before they reach V1b halfway down which explains batching the calls to avoud the few MB data limit..

[@piotrrusak]

Great hidden gems in Power Automate presented Tyler. I will use them definitely 💪

[@tylerkolota]

Thank you, that’s nice to hear!

[@dineshtalekar8980]

@tyler kolota can you please put down details step what all things needs to modify in the flow once we imported the solution. I imported the solution i have record more than 110k but it is not working for me

[@tylerkolota]

@@dineshtalekar8980 Hello, you should just have to set up the initial List rows action to point to your workbook & table. If for some reason you can’t get this flow working, there is this alternate Office Scripts flow that should be even more efficient & includes set-up instructions community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d

[@jmendlozcenit]

I am having problems with the post, Bulk Row Upload to Dataverse from Power Automate, how do you save information in a lookup type column that comes from another data table, or a date column of (MM/dd/yyyy)

[@tylerkolota]

This is an Excel video. Are you talking about the Update a row Dataverse action? Or are you talking about Dataverse batch actions using HTTP calls?

[@jmendlozcenit]

@@tylerkolota Yes, I'm talking about. Dataverse batch actions using HTTP calls. I know it's not from the video, but thank you so much for the information you give us.

[@tylerkolota]

@@jmendlozcenit For the date you can try formatting the date in the UTC Z format. For the lookup, it is more complicated & requires creating a large indexed JSON object so one can reference the correct guid based on a selected unique field like an email address. I did something like that on this batch SharePoint template, but have yet to build such a lookup version for the batch dataverse template. powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoint-Lists/td-p/1365410

[@JustinBouchardw]

I know Powerapps just switching things around...but I can't import this thing. Indicates there are dependencies that need downloading.

[@tylerkolota]

Yeah, I'm just seeing that now. I'm not yet sure how to even import those dependencies. Have you tried using the Power Automate Legacy Import method? powerusers.microsoft.com/t5/Power-Automate-Cookbook/Read-Excel-Faster/m-p/2746922/highlight/true#M2793

[@tylerkolota]

I found an extra Excel connection that didn't originate from the solution. I removed it and the import should be fixed with solution version 6.

[@JustinBouchardw]

@@tylerkolota That did it! Thank you!

[@shinsvaka93]

For ~3250 row count tables and to get sub 10 second read time, I'm finding I have to decrease the batch size to a max of 250 with a max of 4 list row actions in the side of the Switch Device and branch by remainder action. When setting the batch over 250 It doesn't seem to actually pull all the rows in the table, which I thought was odd. Expected?

[@tylerkolota]

@@shinsvaka93 No that’s not expected

[@shinsvaka93]

​@@tylerkolota Understood, thanks for the reply. Should the mul(20000,... be changed depending on the table size? Looking at the outputs of the initial List rows in the Do until Get row count for batches loop, it seems like it doesn't actually return anything except the first row, the second do until loop doesn't give any value for the output?

[@shinsvaka93]

Looks like since I didnt import it, I had to manually turn on pagination, since the Get Rows items were limited by default. Cant believe I didnt realize that was the issue.. Thanks for sharing the flow by the way, very helpful!