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
Employee
Employee

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
Employee
Employee

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

 

 

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



Anonymous
Not applicable

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

Exclusive LIVE Community Event: Power Apps Copilot Coffee Chat with Copilot Studio Product Team

It's time for the SECOND Power Apps Copilot Coffee Chat featuring the Copilot Studio product team, which will be held LIVE on April 3, 2024 at 9:30 AM Pacific Daylight Time (PDT).     This is an incredible opportunity to connect with members of the Copilot Studio product team and ask them anything about Copilot Studio. We'll share our special guests with you shortly--but we want to encourage to mark your calendars now because you will not want to miss the conversation.   This live event will give you the unique opportunity to learn more about Copilot Studio plans, where we’ll focus, and get insight into upcoming features. We’re looking forward to hearing from the community, so bring your questions!   TO GET ACCESS TO THIS EXCLUSIVE AMA: Kudo this post to reserve your spot! Reserve your spot now by kudoing this post.  Reservations will be prioritized on when your kudo for the post comes through, so don't wait! Click that "kudo button" today.   Invitations will be sent on April 2nd.Users posting Kudos after April 2nd at 9AM PDT may not receive an invitation but will be able to view the session online after conclusion of the event. Give your "kudo" today and mark your calendars for April 3, 2024 at 9:30 AM PDT and join us for an engaging and informative session!

Tuesday Tip: Unlocking Community Achievements and Earning Badges

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!     THIS WEEK'S TIP: Unlocking Achievements and Earning BadgesAcross the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. These badges each signify a different achievement--and all of those achievements are available to any Community member! If you're a seasoned pro or just getting started, you too can earn badges for the great work you do. Check out some details on Community badges below--and find out more in the detailed link at the end of the article!       A Diverse Range of Badges to Collect The badges you can earn in the Community cover a wide array of activities, including: Kudos Received: Acknowledges the number of times a user’s post has been appreciated with a “Kudo.”Kudos Given: Highlights the user’s generosity in recognizing others’ contributions.Topics Created: Tracks the number of discussions initiated by a user.Solutions Provided: Celebrates the instances where a user’s response is marked as the correct solution.Reply: Counts the number of times a user has engaged with community discussions.Blog Contributor: Honors those who contribute valuable content and are invited to write for the community blog.       A Community Evolving Together Badges are not only a great way to recognize outstanding contributions of our amazing Community members--they are also a way to continue fostering a collaborative and supportive environment. As you continue to share your knowledge and assist each other these badges serve as a visual representation of your valuable contributions.   Find out more about badges in these Community Support pages in each Community: All About Community Badges - Power Apps CommunityAll About Community Badges - Power Automate CommunityAll About Community Badges - Copilot Studio CommunityAll About Community Badges - Power Pages Community

Tuesday Tips: Powering Up Your Community Profile

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!   This Week's Tip: Power Up Your Profile!  🚀 It's where every Community member gets their start, and it's essential that you keep it updated! Your Community User Profile is how you're able to get messages, post solutions, ask questions--and as you rank up, it's where your badges will appear and how you'll be known when you start blogging in the Community Blog. Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile.     Password Puzzles? No Problem! Find out how to sync your Azure AD password with your community account, ensuring a seamless sign-in. No separate passwords to remember! Job Jumps & Email Swaps Changed jobs? Got a new email? Fear not! You'll find out how to link your shiny new email to your existing community account, keeping your contributions and connections intact. Username Uncertainties Unraveled Picking the perfect username is crucial--and sometimes the original choice you signed up with doesn't fit as well as you may have thought. There's a quick way to request an update here--but remember, your username is your community identity, so choose wisely. "Need Admin Approval" Warning Window? If you see this error message while using the community, don't worry. A simple process will help you get where you need to go. If you still need assistance, find out how to contact your Community Support team. Whatever you're looking for, when it comes to your profile, the Community Account Support Knowledge Base article is your treasure trove of tips as you navigate the nuances of your Community Profile. It’s the ultimate resource for keeping your digital identity in tip-top shape while engaging with the Power Platform Community. So, dive in and power up your profile today!  💪🚀   Community Account Support | Power Apps Community Account Support | Power AutomateCommunity Account Support | Copilot Studio  Community Account Support | Power Pages

Super User of the Month | Chris Piasecki

In our 2nd installment of this new ongoing feature in the Community, we're thrilled to announce that Chris Piasecki is our Super User of the Month for March 2024. If you've been in the Community for a while, we're sure you've seen a comment or marked one of Chris' helpful tips as a solution--he's been a Super User for SEVEN consecutive seasons!   Since authoring his first reply in April 2020 to his most recent achievement organizing the Canadian Power Platform Summit this month, Chris has helped countless Community members with his insights and expertise. In addition to being a Super User, Chris is also a User Group leader, Microsoft MVP, and a featured speaker at the Microsoft Power Platform Conference. His contributions to the new SUIT program, along with his joyous personality and willingness to jump in and help so many members has made Chris a fixture in the Power Platform Community.   When Chris isn't authoring solutions or organizing events, he's actively leading Piasecki Consulting, specializing in solution architecture, integration, DevOps, and more--helping clients discover how to strategize and implement Microsoft's technology platforms. We are grateful for Chris' insightful help in the Community and look forward to even more amazing milestones as he continues to assist so many with his great tips, solutions--always with a smile and a great sense of humor.You can find Chris in the Community and on LinkedIn. Thanks for being such a SUPER user, Chris! 💪 🌠  

Tuesday Tips: Community Ranks and YOU

TUESDAY TIPS are our way of communicating helpful things we've learned or shared that have helped members of the Community. Whether you're just getting started or you're a seasoned pro, Tuesday Tips will help you know where to go, what to look for, and navigate your way through the ever-growing--and ever-changing--world of the Power Platform Community! We cover basics about the Community, provide a few "insider tips" to make your experience even better, and share best practices gleaned from our most active community members and Super Users.   With so many new Community members joining us each week, we'll also review a few of our "best practices" so you know just "how" the Community works, so make sure to watch the News & Announcements each week for the latest and greatest Tuesday Tips!This Week: Community Ranks--Moving from "Member" to "Community Champion"   Have you ever wondered how your fellow community members ascend the ranks within our community? What sets apart an Advocate from a Helper, or a Solution Sage from a Community Champion? In today’s #TuesdayTip, we’re unveiling the secrets and sharing tips to help YOU elevate your ranking—and why it matters to our vibrant communities. Community ranks serve as a window into a member’s role and activity. They celebrate your accomplishments and reveal whether someone has been actively contributing and assisting others. For instance, a Super User is someone who has been exceptionally helpful and engaged. Some ranks even come with special permissions, especially those related to community management. As you actively participate—whether by creating new topics, providing solutions, or earning kudos—your rank can climb. Each time you achieve a new rank, you’ll receive an email notification. Look out for the icon and rank name displayed next to your username—it’s a badge of honor! Fun fact: Your Community Engagement Team keeps an eye on these ranks, recognizing the most passionate and active community members. So shine brightly with valuable content, and you might just earn well-deserved recognition! Where can you see someone’s rank? When viewing a post, you’ll find a member’s rank to the left of their name.Click on a username to explore their profile, where their rank is prominently displayed. What about the ranks themselves? New members start as New Members, progressing to Regular Visitors, and then Frequent Visitors.Beyond that, we have a categorized system: Kudo Ranks: Earned through kudos (teal icons).Post Ranks: Based on your posts (purple icons).Solution Ranks: Reflecting your solutions (green icons).Combo Ranks: These orange icons combine kudos, solutions, and posts. The top ranks have unique names, making your journey even more exciting! So dive in, collect those kudos, share solutions, and let’s see how high you can rank!  🌟 🚀   Check out the Using the Community boards in each of the communities for more helpful information!  Power Apps, Power Automate, Copilot Studio & Power Pages

Find Out What Makes Super Users So Super

We know many of you visit the Power Platform Communities to ask questions and receive answers. But do you know that many of our best answers and solutions come from Community members who are super active, helping anyone who needs a little help getting unstuck with Business Applications products? We call these dedicated Community members Super Users because they are the real heroes in the Community, willing to jump in whenever they can to help! Maybe you've encountered them yourself and they've solved some of your biggest questions. Have you ever wondered, "Why?"We interviewed several of our Super Users to understand what drives them to help in the Community--and discover the difference it has made in their lives as well! Take a look in our gallery today: What Motivates a Super User? - Power Platform Community (microsoft.com)

Users online (6,318)