cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdn4lf
Resolver I
Resolver I

Excel List Rows / Get Tables

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.

 

"message": "The expression \"drives('b!6AtEja-ej0K-HPeZeZER-xNOrKrx5TxMqHavsMmsv3mmdLc0_1J4S79NToRHivKz')/items/\"{87864810-E5A7-4BCD-A930-94BC86D82549},5\"\" is not valid.\r\nclientRequestId: abccceae-1481-4112-853c-df5eee1cac4e",
"source": "excelonline-ncus.azconn-ncus.p.azurewebsites.net"
 
OR
 
 
"status": 400,
"message": "The expression \"drives('b!6AtEja-ej0K-HPeZeZER-xNOrKrx5TxMqHavsMmsv3mmdLc0_1J4S79NToRHivKz')/items/Upload Data3.xlsx\" is not valid.\r\nclientRequestId: 5a55e8b5-1a5c-48be-bc56-b2de4728134a",
"source": "excelonline-ncus.azconn-ncus.p.azurewebsites.net"
}2019-05-14_8-57-48.png
2019-05-14_8-57-33.png
Any assistance would be very much appreciated.
19 REPLIES 19
efialttes
Super User
Super User

@cdn4lf 

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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Thanks.  I've tried the file picker feature and run into similar issues / the file locks me out.

 

Any ideas as to a work around?

@cdn4lf 

I am afraid I also experimented file locking isses some months ago. THere are some posts in this same community covering this topic, like:

https://powerusers.microsoft.com/t5/Building-Flows/Flow-causing-error-code-423-file-locked-by-user/t...

 

Let's hope someone else can point us to a workaround

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



v-zhos-msft
Solution Sage
Solution Sage

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.

Capture7.PNG

Best Regards,

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.

@cdn4lf 

 

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
Frequent Visitor

Hello everyone,
It’s easy to do that with little trick
Please check out my post here

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Alternative-to-GET-rows-Excel-List-rows-...

@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.

 

{
"status": 404,
  "message": "No table was found with the name 'Table'.\r\nclientRequestId: 3447bc11-c323-435f-b57f-d7dc8ea0f555",
  "source": "excelonline-ncus.azconn-ncus.p.azurewebsites.net"
}

 

Any ideas?

Dhayalan
Frequent Visitor

I believe , you have no tables defined for the excel , Please follow this guide to create one

https://www.contextures.com/xlExcelTable01.html

 

If you're Excel has a table , there should be tab called "Design"

Capture.PNG

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.

 

2019-05-16_9-46-06.png

Dhayalan
Frequent Visitor

I hope all the tables has a unique name , just in case

 

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.

@Dhayalan 

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

https://docs.microsoft.com/es-es/connectors/excelonlinebusiness/#get-tables

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Tried that.  Below is the peekcode from the block.

{
    "inputs": {
        "host": {
            "connection": {
                "name": "@parameters('$connections')['shared_excelonlinebusiness']['connectionId']"
            }
        },
        "method": "get",
        "path": "/codeless/v1.0/drives/@{encodeURIComponent('b!6AtEja-ej0K-HPeZeZER-xNOrKrx5TxMqHavsMmsv3mmdLc0_1J4S79NToRHivKz')}/items/@{encodeURIComponent(last(split(body('Create_file')?['Id'],'.')))}/workbook/tables",
        "queries": {
            "source": "sites/[companyname].sharepoint.com,8d440be8-9eaf-428f-be1c-f799799111fb,aaac4e13-e5f1-4c3c-a876-afb0c9acbf79"
        },
        "authentication": "@parameters('$authentication')"
    }
}
 
It wasn't able to find the resource when using a dynamic file.  With a static file, everything works properly including the tables.
Dhayalan
Frequent Visitor

Please share your peekcode for List rows , its has to be end with "tables/tablename/items"

 

As Requested!  I appreciate all the help!
 
{
    "inputs": {
        "host": {
            "connection": {
                "name": "@parameters('$connections')['shared_excelonlinebusiness']['connectionId']"
            }
        },
        "method": "get",
        "path": "/drives/@{encodeURIComponent('b!6AtEja-ej0K-HPeZeZER-xNOrKrx5TxMqHavsMmsv3mmdLc0_1J4S79NToRHivKz')}/files/@{encodeURIComponent('0127HMMTEA3LYJOIHLEVFK2NOSJUNE656Q')}/tables/@{encodeURIComponent('Table')}/items",
        "queries": {
            "source": "sites/[Company Name].sharepoint.com,8d440be8-9eaf-428f-be1c-f799799111fb,aaac4e13-e5f1-4c3c-a876-afb0c9acbf79"
        },
        "authentication": "@parameters('$authentication')"
    },
    "metadata": {
        "flowSystemMetadata": {
            "swaggerOperationId": "GetItems"
        },
        "0127HMMTEA3LYJOIHLEVFK2NOSJUNE656Q": "/TEST3.xlsx"
    }
}

@Dhayalan. Any ideas?

Did you ever solve this?

@Mark2457  I recently found this post that seems to answer the question, provided the table doesn't change.  Karim's solution seems to work nicely.

 

Let me know if that helps

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. 

axorth_0-1601495632238.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,610)