Excel List Rows / Get Tables

[cdn4lf]

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"

}

Any assistance would be very much appreciated.

[efialttes]

@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

[cdn4lf]

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?

[efialttes]

@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

[v-zhos-msft]

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.

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]

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

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]

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

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"

The table name should match one defined in Excel

[cdn4lf]

@Dhayalan , the table is defined.  There are actually 8 or so defined tables within the workbook, including a table generated by a query.

[Dhayalan]

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

[cdn4lf]

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.

[efialttes]

@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

[cdn4lf]

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]

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

[cdn4lf]

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]

@Dhayalan. Any ideas?

[Mark2457]

Did you ever solve this?

[cdn4lf]

@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

[axorth]

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.