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 III
Super User III

@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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Kudoed Authors
Users online (2,586)