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

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

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!

1 REPLY 1
tom_riha
Super User
Super User

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/



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (1,825)