cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdn4lf
Level: Powered On

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.
16 REPLIES 16
Dual Super User
Dual Super User

Re: Excel List Rows / Get Tables

@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

 





If anyone is trying to help you with an issue, the fastest way to say thanks is by kudoing. Be grateful, Thumbs up!

Proud to be a Flownaut!





cdn4lf
Level: Powered On

Re: Excel List Rows / Get Tables

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?

Dual Super User
Dual Super User

Re: Excel List Rows / Get Tables

@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

 





If anyone is trying to help you with an issue, the fastest way to say thanks is by kudoing. Be grateful, Thumbs up!

Proud to be a Flownaut!





v-zhos-msft
Level 10

Re: Excel List Rows / Get Tables

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.

Choots76
Level: Powered On

Re: Excel List Rows / Get Tables

@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
Level: Powered On

Re: Excel List Rows / Get Tables

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-...
cdn4lf
Level: Powered On

Re: Excel List Rows / Get Tables

@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
Level: Powered On

Re: Excel List Rows / Get Tables

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

 

 

cdn4lf
Level: Powered On

Re: Excel List Rows / Get Tables

@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
Level: Powered On

Re: Excel List Rows / Get Tables

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

 

cdn4lf
Level: Powered On

Re: Excel List Rows / Get Tables

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.

Dual Super User
Dual Super User

Re: Excel List Rows / Get Tables

@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





If anyone is trying to help you with an issue, the fastest way to say thanks is by kudoing. Be grateful, Thumbs up!

Proud to be a Flownaut!





cdn4lf
Level: Powered On

Re: Excel List Rows / Get Tables

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
Level: Powered On

Re: Excel List Rows / Get Tables

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

 

cdn4lf
Level: Powered On

Re: Excel List Rows / Get Tables

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"
    }
}
cdn4lf
Level: Powered On

Re: Excel List Rows / Get Tables

@Dhayalan. Any ideas?

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,287)