cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TiagoFreire
Kudo Kingpin
Kudo Kingpin

How to use dynamic files with Excel Read Table connector: a tutorial/workaround - over 9000 !

Hi, 

 

Other users like me have had difficulties doing one specific activity: 

Read an excel table using dynamic file names.

"But it is impossible!" you will tell me. 
Yes, it should be. The Excel "List rows present in a table" should be a very good connector. 
It has one very bad fatal flaw though: It does not accept dynamic file names. 
It explicitly asks the user to select a file using Power Automate's "Browse File" selector. 
This design decision is bonkers and severily limits a variety of ways where this connector could be useful. 

It also goes counter to Power Automate's raison d'etre, of making the user's life easier. 

But it is what it is, until Microsoft decides to make it better. But we have our ways to subvert it...

 

image.png

 

So, we have to do some white hat chicanery to work around the Action's design shenanigans. 

And chicanerize the shenaniganery we will!

1st chicane: 

The first level of chicanery is not perfect, but might workfor your needs: 
1) have a 'working file' with a fixed file name

2) update it with your document contents every time you run the Automation.

 

 

 

Limitations: 

1) you cannot run the flow in parallel. you musthave a reasonable prediction of how lon you need to reserve the file use, and wait for one flow execution before you run it again, or they may step on each other, as the file is overwritten with new content at every run.

2) you must wait at least 10 minutes between runs, regardless of the flow's runtime. Why is that? Because otherwise you will get a dreaded "file is locked for shared use" error. Every time a file is updated, on document library with default settings, it gets this type of lock that is related to co-authoring. It happens even if the only "user" touching the document is Power Automate. This lock lasts for at least 10 minutes, YMMV.

 

10+ minutes between runs is a severe limitation. But all is not lost, it turns out you can bypass the "file is locked for shared use". It's a bit complicated, but doable. 

 

2nd chicane:

bypass co-authoring share lock

To bypass the co-authoring lock, you will need 2 things: 

1) to enable mandatory check-outs on the document library

2) to check out and check in the documents in Power Automate for the update and file reading. 

 

You might find undesirable to enforce mandatory check-outs in your document library, as co-authoring is a valuable tool. 
I would recommend creating a second document library just to house the document conversion, and enforce mandatory check-outs there. 

 

image.png

Library Settings: 
image.png

Versioning Settings:

image.png

 

After you have the library set up, save your "Convert.xlsx" dummy file in the "Convert" document library. 

You should then be able to set up the Power Automate flow.

YOU WILL HAVE TO MANUALLY CHECK OUT THE EXCEL DOCUMENT WHILE YOU EDIT THE FLOW, otherwise Power Automate will not be able to read it. CHECK IN the file after you are done. 

Here is what is necessary to be done, in pictures: 

 

image.png

 

1) Your flow will be triggered "for a selected file" on SharePoint. This will get the file ID. 

2) read the file contents using path, so you can update the "convert.xlsx" fiel afterwards.

3) read "convert.xlsx" metadata to get its File ID, so you can work with it. 

4) check out "convert.xlsx".

5) update "convert.xlsx" with the file content read on step 2).

6) check in the file to commit and publish changes to the document. 

7) check out the file, again. Yea, you must commit the changes first before reading it.

😎 list the rows from the file "convert.xlsx" - you will get your fresh content, with tutti-ftutti smell!

9) since you have not changed "convert.xlsx", you can just discard the check-out rather than checking it back in. 

 

At this stage, you have all the table contents from a file that has been selected dynamically on SharePoint.

You can do whatever you want with it. 

In my case, I have populated a Word Template with answers from a checklist (excel comes from a mobile checklist platform), and saved a Word and a PDF copy of the contents with the company stationery and logo for archival and to submit to clients. 

 

It was a bit of blood, sweat and tears, but at least I figured it out. 

As the process is WAAAYYYY more convoluted than it should be, I decided to post it here, as more people might find it useful. 

 

Cheers!

0 REPLIES 0

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,548)