Unanswered

Dynamic Filename: List Rows from Excel Table

(0) Share

Report

Posted on by 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?

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).
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?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

ScottShearer 25,270 Most Valuable Professional on at

Like (6)

Report

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

Was this reply helpful? Yes No
m_williamson 17 on at

Like (0)

Report

@ScottShearer

Spectacular. Thank you!

Was this reply helpful? Yes No
Tim_S 19 on at

Like (0)

Report

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

Was this reply helpful? Yes No
dtunforgettable 6 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
amulhearn0207 109 on at

Like (0)

Report

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

Was this reply helpful? Yes No
amulhearn0207 109 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (0)

Report

@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

Was this reply helpful? Yes No
ceasar3000 2 on at

Like (0)

Report

Can you please add the action Add row into a table. I'm getting nulls

Was this reply helpful? Yes No