Update Excel file in SP Library and then Read the Rows from a Table

[mboonie]

Hi, all.

I need to have a flow that imports and uploads the latest formatted version of an Excel report (using Tables, etc.) and uses it to update a SP list.

The issue that I'm having, of course, is that when I upload a new version of the file--even though it's the same name, same worksheets, same table-names--the workflow can't find anything (e.g. the tables, rows, etc.) presumably because it's a new file?

I read a solution that leverages the ID of the file and tables after using a "Get Tables" or "Get Rows" action, which kinda makes sense.  But when I want to read the data in the rows to create the SP list item, there are no column names to choose from (because I'm not selecting a specific, existing file when I'm writing the workflow, because I'm using the ID's of 'unknown' items instead of hard-coded names and paths.)

Since I know the names of the columns in advance, I though I could just write them as expressions, anyway (e.g. I can just type

"items('Process_each_row')?['Record ID']" as an expression.) But if I haven't hard-coded the path and the filename and the table name, Power Automate just tells me it can't access anything by that name.

So, is there a proven way to accomplish this?

1. Upload/replace properly-formatted Excel file in SP library
2. Read the rows from two specific tables that I know will be in that file
3. Use the data to create new list items in a second SP list

It seems to be the uploading of a new file that breaks everything. 

Thanks in advance for any help!

[tom_riha]

Hello @mboonie ,

my guess is that it breaks the flow because the ID of the file changes. Power Automate works with file IDs, and when you replace a file, it'll delete the original file on the background and create a new file with the same name but with a different ID.

Take a look on this post by @Jcook , he described how to handle the Excel file replacement in a flow: https://flowaltdelete.ca/2020/06/25/power-automate-excel-dynamic-file/