Dynamic Filename: List Rows from Excel Table

[m_williamson]

Hi all,

I have a Flow that will extract an Excel file sent in the attachments of an email (which is sent every few days), then save this to SharePoint and copy the rows from this table into a Master Excel file also stored in SharePoint.

The Flow worked fine when the List rows present in table connector used a static file (that I selected from that file input) but, in reality, a new file with that day's date will be sent periodically and it needs to handle these dynamically. The table in this file will always be called 'Table1', and the file will always be {insert file name}_YYYY-MM-DD.xlsx where the red is, of course, the date for that day. When I tried running it with the above to handle the dynamic nature, I got the following error:

The parameter 'file' has an invalid value '{INSERT FILE PATH}/sec_form_D_2020-12-01.xlsx'.

When, in fact, the file does exist on account of the earlier steps. Any suggestions for how to get this to work?

1. An alternative I tried was to carry the file along from earlier, but that requires me to put the List rows present in table within an Apply to Each, which, then, does not work with the following Apply to Each for each row (final step).
2. I, then, tried to take the second step out of an Apply to Each and put the List rows after that but Flow automatically makes creating files in SharePoint from dynamic attachments an Apply to Each

I am now stuck, any ideas for how to get this to work dynamically?

[ScottShearer · Accepted solution]

@m_williamson 

Unfortunately, you can't simply provide a string with the path and name - you need to provide the file identifier.  In order to get the file identifier, you need to retrieve the file properties from SharePoint. 

Here is a link to the documentation

See my example below.

In my example, I hard code the filename in a compose action but you can use an expression as well.

Please note the use of FileLeafRef in my OData filter - that is really the file name with extension.

[m_williamson]

@ScottShearer 

Spectacular. Thank you!

[Tim_S]

Is is possible to do this by using a dynamic site address and dynamic file name?

[dtunforgettable]

When I do this it works, but I also need to get the information from the table in the excel so I can create a condition to determine who the email is going out to, but by doing it this way I cannot get the table info. 

  Any suggestions on how to do this. 

[amulhearn0207]

@ScottShearer  i knew if I stalked your activity I'd find something to get me on the right track. 
I'm in a similar situation. I need to look up an Excel file (the file deletes + recreates itself daily on One Drive). It's text name is always the same (Bookings Advanced Find View) but its 'Name' (ID) changes, of course.

Should I expect these same steps to solve my issue (of course, using get file properties for One Drive ilo SharePoint)?  

I'd go test it myself but I've been at it for like 16 hours and left to get some food - thought I'd see if I might get an answer before I get home.

MANY MANY THANKS!

[amulhearn0207]

@ScottShearer if by any chance you're seeing these - I think (hope) I'm close, but I'm not using the right verbiage in the Get File Data item. Been looking and looking but haven't figured it out yet.

[ScottShearer]

@amulhearn0207 

You are going to want to use a Find files in a folder action first.  Follow that up with a Get file metadata action

Here is a link to the find files documentation