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
@Dhayalan , the table is defined. There are actually 8 or so defined tables within the workbook, including a table generated by a query.
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.
This is junk, what good is a trigger on "created" if I can't take any action on the file that was just created? I should just be able to set the file to the name of the file from the trigger.