cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbaldwin1
Frequent Visitor

Excel (or CSV) to sharepoint list

New flow user here. 

 

I am trying to figure out if it is possible to create a flow that will monitor a sharepoint online/365 document library or ondrive folder for the addition of a new file with the name containing "Roster" and import the contents of the file into an existing sharepoint list.

 

Does anyone have any templates like this or examples of how to get started?

 

Thanks in advance,

Dave

1 ACCEPTED SOLUTION

Accepted Solutions

1. Double-check to make sure your Table in Excel has a range that includes all your rows.

2. Not sure what the purpose of your condition statement is. Are you trying to filter out any blank rows or rows with blank cells? It may be worth removing the condition and running the Flow without it.

3. I have used a similar Flow, but I wrapped the Create item action in a loop (Apply to Each) and this seems to work well.  I do not have any conditions, since I was not trying to filter out any rows.

4. In your List Rows action, click on the Advanced Options, and check your settings on total count or skip.  The skip field should be blank, but it may be good to put 1,000 into the top count field.  I have found that if you leave this field blank, sometimes it inserts an arbitrary limit, like 100.

 

See screenshot below. If this works, mark this as a solution.Flow_Excel_extract_rows.JPG

View solution in original post

18 REPLIES 18
JmArreola
Employee
Employee

Hi @dbaldwin1

 

I attached the screenshot on how to do that Flow.

 

1. Go to https://flow.microsoft.com/manage/flows/new

2. Select 'When a file is created' in onedrive (SPO/O365...)

3. Click on New Step -> Add a condition

4. In the 'Object Name' field select 'File Name'

5. 'Relationship' field select 'contains'

6. In 'Value' field type 'Roster'

7. Click on New Step -> Add an action

8. As the action select Sharepoint/ Create item.

 

Capture.PNG

This is great!  I didn't realize it would work this way.

I am not sure how to map the data though.  When I point it at my list it reads in the list columns and lets me select what to put in them but can you share the syntax for mapping a column from the CSV to a column in the list?

 

 

 

Capture.PNG

not sure what to do here.

@dbaldwin1 your scenario is a lil bit strange because the flow designer can't parse the columns unless you point to a specific excel file.

 

What i mean is, if, instead of using the trigger every time you get a file named 'bla' you set a recurrence to a specific excel file called 'bla', we can parse the columns and you can use them in the designer.

 

I would change your approach instead and i would build the following flow:

 

1. Create a file in your SPO/O365/Onebox called 'Roster' with the schema that you expect.

2. Go to flow.microsoft.com/manage/flows/new

3. Add a recurrence trigger

4. Add an action -> excel get rows pointing to the file called 'Roster'

5. Click on New Step -> Add an action

6. As the action select Sharepoint/ Create item.

7. In sharepoint, you will see the columns from the file created in step 1.

Capture.PNG

 

This is almost there.  I can adjust my needs since this is very early on.

I built as you proposed but when it reads the file it does so several times, creating many duplicate rows in sharepoint list.

Also, it seems that the Excel get rows only reads from OneDrive and not sharepoint document libraries, is that right?

I had this same question, as I was also attempting this.  I came to the same conclusion that it did not seem possible to parse fields from an Excel file within SharePoint.  Or is it possible that you just use the OneDrive file as the initial template to set up the parsing schema, then it would work on incoming files to SharePoint that had the same schema?

I got same issue, it import duplicated rows to sharepoint list. is there any update on that? Regards Steven

I like where your going with this, but the problem I run into is that when using the Excel Get Rows, I can't connect to a SharePoint on Prem, SharePoint Online, or anything but one drive and a few other connectors. I would just copy the file to my one drive but I can't find a flow that can copy an excel file saved in a SharePoint Library and copy it over to One Drive for Buz. Everything is One Drive towards SP.

 

Any clarification would be greatly appricated. I for one am just trying to one a week, have 1 specific table in an excel file be read, and that data copied to a SharePoint list, where it will create a new row for ever row in the excel table. It's basically a summary tab which will act as a history of the file in the SharePoint list. The reason for this is every week, this summary tab is changed with new data and I know we have versioning, but we just want this one table copied out as a quick way of doing a look up instead of digging back through past versions.


Thanks,
Rob

Also do you know if .xlsm will be supported in the future? Get Rows can't see that type of file, but was successful in testing with a .xlsx file.

I gave up on this long ago, as it forced me to use OneDrive to send data from an outside form source (Forms on Fire) through OneDrive to SharePoint. Since then, Forms on Fire, a great mobile forms design/deployment software, upgraded their software to allow direct transmission of data fields from their forms to Sharepoint lists.  I do not work for Forms on Fire, but they are a great company, and we have been using their software (subscription based) for almost a year now.

 

They have a free 14-day trial period, and a great tech support team.

You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works.

 

Then you can iterate throug result array and insert data into a SharePoint list.

Anonymous
Not applicable

Hi, I'm a new flow user and got a flow to take the rows of an Excel sheet and transfer it to a Sharepoint list to work for the most part. When I ran my flow, all of the rows from my Excel sheet transferred to my SharePoint list except for the last 13 rows. I tried refreshing the SharePoint list and running the flow a few times after, but I have no luck in getting the remaining rows to my list. I'm confused by this considering that I have only 270 rows in my Excel sheet, which is well below the maximum item number for SharePoint lists. Do you have any suggestions for how I can remedy this? Any help is appreciated. Thanks!

Firstly, make sure your Excel file has a table, with a name, and that all the rows you want are included in that table. Second, see if you have any columns in the list set to "Enforce Unique values," and if you do, see if you are trying to submit records that have that same value (they would then be prevented from loading to the list). Then, if that doesn't solve it, post screenshots of your Flow here, with all detail shown. 

Anonymous
Not applicable

Hi @Runner55552 , thank you for your suggestions. That didn't quite solve the issues so I'll post my screenshots: flow 1.PNGflow 2.PNGflow 3.PNG

In Condition, the first function is: empty(body('Get items')?['value']). This flow ran successfully as well which I'm not sure as to how if there were rows excluded. Thank you!

1. Double-check to make sure your Table in Excel has a range that includes all your rows.

2. Not sure what the purpose of your condition statement is. Are you trying to filter out any blank rows or rows with blank cells? It may be worth removing the condition and running the Flow without it.

3. I have used a similar Flow, but I wrapped the Create item action in a loop (Apply to Each) and this seems to work well.  I do not have any conditions, since I was not trying to filter out any rows.

4. In your List Rows action, click on the Advanced Options, and check your settings on total count or skip.  The skip field should be blank, but it may be good to put 1,000 into the top count field.  I have found that if you leave this field blank, sometimes it inserts an arbitrary limit, like 100.

 

See screenshot below. If this works, mark this as a solution.Flow_Excel_extract_rows.JPG

Anonymous
Not applicable

@Runner55552this worked, thanks for the extensive solution!

Let me know which specific item worked. Was it the For Each Loop, or the removing of the condition, or other?

Anonymous
Not applicable

@Runner55552  It was the For Each loop. Thanks again!

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (3,888)