cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alibemday
Regular Visitor

When a New Excel File is Created in SharePoint, Get Table Rows and Create New Items in SharePoint List

I am trying to perform a flow to do the following:

  1. New Excel File gets created in a SharePoint library
  2. Get rows from table
  3. Add rows to SharePoint List, DO NOT need to review to update or delete items, just need to create new list items.

NOTES: 

  • New files will always only have one table and have the same column headers.
  • If rows in Excel are not yet in a table format, is there a way to do a flow that will take all data and turn it into a Table first?
  • Excel may come in with certain rows filtered out, will that cause any issues in what gets copied to the List? We only want the non-filtered rows to come over into the List.

 

I've tried different ways to grab the data, but the solutions I see all include updating the data, which I don't want it to do. I only want new items added. Below is the most recent one I've been trying to create to make it work. Please help! Thank you 😃

 

image.png

5 REPLIES 5
mahoneypat
Solution Sage
Solution Sage

To easily get the data out of Excel it needs to be in cells formatted as a table.  You can save the files as a csv instead of Excel and then parse the CSV data to create the SharePoint items (but that requires a few custom steps to do that).  Or you should be able to use an Office Script to first format the data as a table automatically.

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Okay, thanks; that's what I expected. Let's assume it is in a Table format, how do I finish out my flow to identify which columns to match to my list? 

mahoneypat
Solution Sage
Solution Sage

I don't think the filters in the Excel table are propagated in your flow.  And you can add an Apply to Each step with the output of your list rows in Excel step, and do a Create Item to create each row as a new item in your SharePoint list.

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


skillsy
Advocate II
Advocate II

Hi @alimbemday,

   First you need to read through the table. I've called my excel spreadsheet 'dataTable' and I am only looking for a status with the word 'entry' to show you how you can filter your data. 

 

skillsy_0-1637368692666.png

You then have to read through each row using an Apply to Each for the Value

skillsy_1-1637368832621.png

You can then in the apply to each, create a new sharepoint list item. There is a mapping option but this is rarely needed. This shows you can use the spreadsheet, look up the user from Active Directory or use an expression (utcNow etc.) to populate the new list item.

skillsy_2-1637369157864.png

 

alibemday
Regular Visitor

I'm not quite following; in my flow, I'm already using list rows present in table and create item. 

 

Additionally, I'm not using one file that continually gets updated. It's always a brand new file, so the flow needs to be dynamic. I'm not understanding how I can get the flow to identify which rows to use in a new file and then put them in the right place in the SharePoint list.

 

Sorry if your solution above answers that, I'm new to all of this, so I may be just misunderstanding what you're trying to say above. 

 

Thanks!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (6,669)