Get rows from latest Excel file in folder

[farhandsome]

Hi all,

I had a quick question - I have an export from SAP coming in nightly to a folder as an Excel. Each time it uploads, it uploads as a new file with the date in the name of the file. 

Would it be possible for Flow to pull the latest item in this folder and update an SP list with these values? The flow would look like this:

Recurrence (nightly)

1. Delete current contents of SP List
2. Get rows of newest Excel file in folder
3. Add all rows of Excel file into SP list

Any help would be very appreciated!

Thanks,

Farhan

[Gareth1 · Accepted solution]

Hi @farhandsome 

There are a couple of ways to do this depending on when the Excel file is been generated (schedule wise)

If SAP is producing the report at a similar time that you want to update Sharepoint, instead of using a scheduled flow you could consider triggering the flow when a new file is created. This way you wouldn't need to worry about selecting the correct file as the details for the file would come through in the trigger

However if you want run it based on a schedule then it gets a little bit trickier. It looks like the max function requires integers / numbers to find the highest value. So the date would need to be converted to a number first using the ticks expression and then once the maximum tick is found you can filter the list of files to select the correct one

You should then be able to use the delete item action and delete all the items

You can then loop through the rows of the table and insert them into the Sharepoint list

If you want to look a having the trigger run when the file is created let me know and I can provide some help with that option instead

[farhandsome]

@Gareth1,

Awesome, thank you for the reply! I am not necessarily tied to a schedule - using a trigger when a new file is created will be more than perfect for my application.

I imagine the flow would be similar in that case?

1. Trigger: When file is created
2. Get items of that file
3. Delete rows of current sharepoint
4. Insert all rows of file into newly blank sharepoint 

Is this correct?

Farhan

[Gareth1]

Hi @farhandsome 

Yes that is correct you will be able to get the ID of the file from the file and pass it to the List rows present in a table and won't need to worry about finding the latest file based on the date

[farhandsome]

Hi @Gareth1,

Sorry to bring up an old topic, but the "List Rows" feature works if the file is created in a OneDrive folder. However, these Excel sheets are being uploaded to a network drive and I am having to use the "File System" connectors rather than the "OneDrive" connectors.  See attached:

This brings up an issue that the "Get file content" connector does not automatically infer the content as an Excel sheet and when I create a file in the OneDrive, it is not automatically assumed as an Excel file. Is there anyway to get the file contents and use flow to turn them into an Excel file or better yet, a SharePoint list?

[metrognome]

Sorry to necro this post but it's super helpful and very informative so I'd like to ask a quick question. 

@Gareth1  At the final step, in the screenshot you indicated that you can update the SP list the the EXCEL content simply by quoting the title but not matching 1-1 every single column?

If the tables (EXCEL table and SP list) have identical structures (number of columns, headings, cell formats etc.) can this create an entire SP Item just by using the EXCEL Table title?

Up until now, I was matching fields one to one which was a bummer to do for all sorts of reasons.

Can I skip this hassle below 

and simply go with the title and expect this to populate all columns accordingly?