cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Get rows from latest Excel file in folder

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: Get rows from latest Excel file in folder

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

 

Screen Shot 2020-01-05 at 6.52.48 pm.png

 

Screen Shot 2020-01-05 at 6.52.57 pm.png

 

Screen Shot 2020-01-05 at 6.57.47 pm.png

 

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

Screen Shot 2020-01-05 at 7.14.39 pm.png

 

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

Screen Shot 2020-01-05 at 7.15.10 pm.png

 

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

View solution in original post

4 REPLIES 4
Highlighted
Resolver IV
Resolver IV

Re: Get rows from latest Excel file in folder

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

 

Screen Shot 2020-01-05 at 6.52.48 pm.png

 

Screen Shot 2020-01-05 at 6.52.57 pm.png

 

Screen Shot 2020-01-05 at 6.57.47 pm.png

 

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

Screen Shot 2020-01-05 at 7.14.39 pm.png

 

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

Screen Shot 2020-01-05 at 7.15.10 pm.png

 

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

View solution in original post

Highlighted
Helper III
Helper III

Re: Get rows from latest Excel file in folder

@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

Highlighted
Resolver IV
Resolver IV

Re: Get rows from latest Excel file in folder

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

Screen Shot 2020-01-06 at 7.10.11 am.png

Highlighted
Helper III
Helper III

Re: Get rows from latest Excel file in folder

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:

MS Flow SYK Daily Error.PNG

 

 

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?

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (10,724)