cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeggyLang
Helper V
Helper V

Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

I have numerous excel expense files to approve and process weekly.
I have an excel template so each file is formatted exactly the same.
I need to create a 'running list' of all line items from all of the excel files.

My thought was to create a flow to add each row of each expense report to a Sharepoint List.

I have created a Sharepoint list with columns that correspond to each column in the excel file.

 

Screenshot of Sharepoint List

PeggyLang_2-1617477178990.png

 

Screenshot of excel file

PeggyLang_12-1617478594352.png

 

Screenshot of the beginnings of my flow. 

PeggyLang_11-1617478017351.png

 

I have already run into a couple of issues;

  • I am unable to access 'Name' data from excel file in 'dynamic content' so that I can populate into 'Title' column of Sharepoint list
 
 

PeggyLang_8-1617477796005.png

 

 
  • There is no dynamic content (I am looking for 'Total' from the excel file) available to populate 'Amount' column of Sharepoint list - I don't understand why not

PeggyLang_9-1617477890647.png

 

  • Currently flow is referencing a specific file.  I need flow to act on each new file.

PeggyLang_10-1617477940051.png

 

Greatly appreciate any help.

 

Thanks!

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @PeggyLang 

 

It looks like spaces are not supported in Excel Field names https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Have-Consistency-in-on-Filter-Query-and-Ord...

https://powerusers.microsoft.com/t5/Building-Flows/Simple-Question-Filter-Query-Where-Excel-Column-N...

 

I've tried the same and couldn't get it to work.  Are you able to adjust your table so that the columns do not have spaces in the names?

 

Alternatively you do the filter using filter array, you would get all rows and them use the filter array action with the column is not empty.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

 

View solution in original post

16 REPLIES 16
DamoBird365
Super User
Super User

Hey there @PeggyLang 

 

I like your documentation and explanation.  What application did you use for that?

 

Regarding your flow, Get Row will return 1 row only and relies on a key column with unique value to return a row.  So if you had a column ID running incrementally from 1, you could supply 4 as key value for ID and return that row.  It looks like you have misunderstood the action and are supplying file content?

 

Most likely you want to use "list rows present in a table" if this is based on a new file being created.  When it comes to supplying dynamic content to the action, you can select "enter custom value" and choose your dynamic loaction/ library/file/table.  You might need to build some of these strings in a compose first and the use the compose in the list rows action.

 

When I comes to why is name not available as dynamic content, I am not sure on that one.  Maybe trying the above action will correct this?

 

Do you want to give it a go and let me know.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

@DamoBird365 

 

Hello Damian,

 

Thank you for responding - and on Easter Weekend to boot.

 

I use 'Snagit' for my screenshots and embellishments.

 

So I learned A LOT yesterday.  You are correct, I needed to go to 'List rows present in table'.  I ran into a BIG snag when I discovered that dates from Excel are not represented in SP list as I had expected, so had to segway into figuring out how to deal with that.

 

Currently my flow is WORKING - sort of.

 

Below is a screenshot of my simple flow.

 

PeggyLang_0-1617537400957.png

 

Below are breakouts of the numbered sections.

 

PeggyLang_1-1617537451092.png

 

PeggyLang_4-1617537558727.png

 

 

PeggyLang_3-1617537474395.png

 

My flow 'kind of' works. 

  • There are empty rows in my excel table.  It is a protected worksheet with a table and x# of blank rows for entry.  So if you have less than x# of rows to enter there ends up being blank rows.  (Perhaps I need to investigate building a 'fillable' form in Excel??)  Flow seems to run smoothly until it comes across a blank row. 
  • I'm hoping there is a simple fix to that?  i.e., check to see if the row is blank first?  but not sure as the action is 'list rows present in table'.  The below is screentshot of error message.

PeggyLang_6-1617538621740.png

 

  • The other issue is I will have upwards of 40 expense reports per week.  I can make sure they are all filed in the same folder and make sure the naming protocol of each file is consistent, but I need flow to act on each new file in the folder as opposed to a specifically named file.  I have not yet figured out how to accommodate that.

Below is a snapshot of my SP list.  There are 38 items in it. 

  • The first 19 'Title' was 'Last, First'. 
  • The second 19 'Title' is 'Lang, Peggy'. 

To test the flow I did point the flow to the very specifically named different and unique files, but as mentioned going forward I need flow to act on each now file in the folder.

 

PeggyLang_5-1617538292963.png

 

So that's where I currently sit.  I'm not very learned with flows and I have an intermediate working knowledge of excel.  I rebuilt the expense report file to include numerous conditional formats, drop down cells, pivots and such and then I built the SP list (from the excel file as opposed to blank - how sweet was that!) and then I built the flow.  The entire process probably took me 6 hours yesterday - someone much more learned than I would have taken much less time - but it is an accomplishment for me and I hope 3/4 of the way complete now.

 

Again thanks for your help.

DamoBird365
Super User
Super User

Hi @PeggyLang 

 

Thanks for the SnagIt tip - will take a look.

 

For your blank or empty lines you could perform a filter on the data during the list rows, expand the advanced settings and create a formula like columnname ne '' and that should remove your blank rows.

 

The alternative is to use the filter array action and perform something similar empty(columnname) is not equal to true.

 

You mention problems handling all files?  You were previously using when a new file is created trigger but now a reoccurrence?  What made you change this?  The new file trigger would run for all files as they are dropped into the folder.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

 

@DamoBird365 

Damien,

This 'project/need' has just come up via a client request and I am just building and in testing phase at the moment.  This has not previously been working and suddenly now not working.

My concern is that the action 'List rows present in a table' needs a very specific file with name

  • 'expense template wip formatted as table protected with full name' (this is named such just for testing purposes). 

The 40 or so files I will be putting in the folder which I need to run through the flow will be named;

  • Doe, John 210301
  • Smith, Frank 210308
  • Young, Joe 210315
  • etc.

 

PeggyLang_0-1617621773301.png

 

So I need flow to look at all new files instead of needing to be told which specific file to look at.  Does this make sense?

 

I will work on the solution for blank row that you suggested.  Thank you.

Hi @PeggyLang 

 

If I do this:

DamoBird365_0-1617623225092.png

 

It fails

 

but if I try this:

DamoBird365_1-1617623262600.png

 

If runs OK.

 

So, the important thing is realising you can do this with dynamic file names but you will need to get the file ID and not he file name.  I notice the that table ID is not a name either but an ID.  

 

You can get this using get tables and assuming it's the first and only table in each file, this would work (compose is file ID, the compose 3 is the table ID from get tables).

 

DamoBird365_2-1617623552165.png

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

 

@DamoBird365  this is awesome.  Thank you so much.  I'm tied up at the moment and can't try it, but certainly will later today and let you know.

Wow!  The learning here is priceless.
So I have reworked the flow such that when triggered the 'filename' is dynamic.  So this works when a new file is created in the specific folder.

 

PeggyLang_0-1617651136854.pngPeggyLang_1-1617651237403.png

My only hiccup remains when the flow runs into an empty row in the table.  Have yet to solve for that.

Hi @PeggyLang 

 

Good to hear this has been helpful, don't forget those kudos and solution marking 🙂

 

I tried the following for empty rows and it works for me:

DamoBird365_1-1617651512061.png

 

DamoBird365_2-1617651535334.png

 

Length of Value array is 5

 

DamoBird365_3-1617651566402.png

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

 

 

 

@DamoBird365 I must be getting the syntax wrong.

The first column in my table is 'Full Name'

PeggyLang_1-1617652405274.png

 

The flow fails - see error message below.

PeggyLang_2-1617652428262.png

 

I need flow to stop when it gets to a blank row.

 

Hi @PeggyLang 

 

It looks like spaces are not supported in Excel Field names https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Have-Consistency-in-on-Filter-Query-and-Ord...

https://powerusers.microsoft.com/t5/Building-Flows/Simple-Question-Filter-Query-Where-Excel-Column-N...

 

I've tried the same and couldn't get it to work.  Are you able to adjust your table so that the columns do not have spaces in the names?

 

Alternatively you do the filter using filter array, you would get all rows and them use the filter array action with the column is not empty.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien


P.S. take a look at my new blog here

 

 

View solution in original post

@DamoBird365 

I have updated the excel column heading to remove space - still not working.

I don't understand your comment referencing 'filter array'.  How and where and what is the syntax of filter array?

@DamoBird365 

IT WORKS!!!!!!  Thank you so much for persevering through this with me!!!

PeggyLang_0-1617672143877.pngPeggyLang_1-1617672173166.png

PeggyLang_2-1617672196898.png

 

Well done @PeggyLang !  🙂 Glad to be of assistance.

Hello @DamoBird365 

This flow is saving me SO MUCH time and allowing me to provide the level of detail client has requested.  Again I thank you for helping (perservering) me through it.  

 

I have been running the flow (for 2 months now) by way of selecting each file and running it through my flow.

PeggyLang_0-1625248008342.png

 

I chose to do it this way for 2 months so that I could;
 - ensure flow was working correctly
 - enlighten me to errors (all data entry) that were causing the flow to 'fail'

I'm in a position now (for next months billing cycle) to have the flow trigger when all of the individual expense files are moved to the 'processing' folder.

So I have attempted to duplicate the flow such that it is triggered when 'a file is created in a folder' as opposed to 'for a selected file'.  I'm unable to complete this as I am unable to either select 'ID' from dynamic content and/or do not know how to obtain the 'ID' in any other method.  (I'm thinking there is probably a way to obtain the 'ID' by using a 'compose', however, I haven't figured it out yet.

 

The below screenshot is first 2 steps of flow that is currently working.

PeggyLang_1-1625248369428.png


I thought it would be as simple as changing the trigger to 'when a file is created in a folder'.  However, when I do that I am no longer able to select 'ID' from 'dynamic content'.  As can be seen from the screenshot 'no dynamic content available'.

PeggyLang_2-1625248583476.png


I'm a bit stumped.



Hello, I am facing a similar scenario - I am working on update sharepoint list data when an excel file created in a folder. Would you like to answer this question? thanks in advance. 

solarson1989_0-1632289865063.png

 

If I understand you correctly, the trigger for your flow will be 'when a file is created in folder'.  So your flow will not run until a file has been created in the folder.

 

The columns on your list need to match/be present in your excel file. 

 

Does this help?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors
Users online (2,774)