I'm trying to build a flow that loads a dynamically named excel for Business Document, loads a static named table within it and copies the rows to SQL.
I'm stuck with List Rows Present AND Get Tables, both not being able to find the resource and/or various other errors. I have tried all possible file identifiers and consistently get the same version of the message.
I played some months ago with Excel connector, and according to the Flow Team; Microsoft flow supported to select a file from the picker in the "Excel (Business)" connector, but choosing a dynamic content in the "File" field to select a file was not supported in Microsoft flow "Excel (Business)" connector.
We posted this feature as an idea: https://powerusers.microsoft.com/t5/Flow-Ideas/Dynamic-content-in-File-amp-Table-inputs-Excel-Busine...
I haven't tested it since then, so unsure if Excel connector currently supports this feature
I am afraid I also experimented file locking isses some months ago. THere are some posts in this same community covering this topic, like:
Let's hope someone else can point us to a workaround
Hi @cdn4lf ,
I am afraid that there is no way to achieve your needs in Microsoft Flow currently.
The actions of Excel only support the already exist files currently.
You can only Select an Excel file through File Browse.
Community Support Team _ Zhongys
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I've been playing with a similar flow, using daily uploads to a sharpoint folder to output to a SharePoint list. The only way I found to overcome the issue was to create a "staging" file with fixed name dump the new data into, and then lift out what I need from there.
The file locking issue cropped up when testing as Flow apparently counts as a "user" and doesn't let go of the file for some time, but since my flow only runs daily, it seems to be ok.
My biggest issue is getting the Excel connector to create a table on a second tab...
@Dhayalan , I was able to make the expression go and can now get the file to load. My issue now, is that it's not grabbing the table correctly and I'm getting a table not found error.
I believe , you have no tables defined for the excel , Please follow this guide to create one
If you're Excel has a table , there should be tab called "Design"
The table name should match one defined in Excel
They do. Checked and rechecked.
I've also tried to do an extraction of the data and save it as a new file, with a new table and it errors out.
The last thing I tried was to use your expression for get tables, but that failed as missing properties.
There is an action block to list all the Tables in an excel file. If you try it, this way you can doublecheck if all the tables are identified by Microsoft Flow
Hope this helps
Tried that. Below is the peekcode from the block.
Look out for new contribution recognition badges coming SOON!
Join us for the first ever Power Platform Online Conference!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!