cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
farhandsome
Helper IV
Helper IV

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
Gareth1
Resolver IV
Resolver IV

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

5 REPLIES 5
Gareth1
Resolver IV
Resolver IV

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

@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

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

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?

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 

BROLT5w

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (1,806)