cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Phineas
Post Prodigy
Post Prodigy

Flow to Move Data From SP Library Document to SP List

Before a I spin my wheels in  a mud rut, can someone validate my conclusions, please? 

Action: 
1. Once a day an Excel workbook is uploaded to a SharePoint Library
2. I need to trigger a flow that 'Get items' from that workbook
3. Flow needs to copy the data from the workbook to a corresponding SharePoint List

My Flow Solution Starts with:

1. Create a 'Automated Cloud Flow'

2. Trigger = 'When a file is created in a folder'
3. Select Site and Library
4. Which action comes next?: Get Items; Get file metadata; Get file properties

8 REPLIES 8
SebS
Impactful Individual
Impactful Individual

Hi @Phineas,

 

This is more like Power Automate topic rather Power apps but here it is :

 

After You set the trigger and the file exist in Library You should use "List rows present in a table" than from SharePoint flow use "Create item" and Map Excel  to SharePoint List.

 

The only challenge you will face if one of the Excel Table columns will store Dates.

Power Automate don't like them for some reason and You will need to create variable what will extract each invidual date column and than use expression to convert them to string (initialize variable"  for each column with date you will need to do that call them varDate1 and if you have more change it to varDate2...

Than add an “Apply to each” action using the “value” output from the Excel “List rows present in a table” action.

And  Add “Set variable” actions for each of your date columns and set the value to the Excel date columns.

 

Now in "Create item" You will be able easy map Your Data Columns by using below Expression remember  to change varDate1 to variable You want to map

 

  • addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)

Also keep in mind Dates can't have Time on them that will brake things out as well if data you passing have dates modifie above formula to 

  • addDays(‘1899-12-30’,int(first(split(variables(‘varDate1’), ‘.’))),’yyyy-MM-dd’)

 

 

Hope it helps

 

Regards


If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users to find it.

I go it to this point. It was working fine when I only had  one item in the SP List.

I deleted that item. I added three new items. Now that flow is looking for 'ID' 1, but it has been deleted.

The first 'ID' now available is 5. How to I update the flow to  correct this issue?

Phineas_2-1660150325262.png

 

SharePoint Library: Excel Spreadsheet

Phineas_1-1660150221387.png

 

SebS
Impactful Individual
Impactful Individual

Hi @Phineas 

 

The Issue You have is that you using Update Item action and that will look for match ID You need to use Create item Action.

If you looking for flow what will recognise if an item exist than update then you using update item but than you need to add condition example if ID exist and match ID in excel Update the item if no Create new item 🙂

 

 

Regards


If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users to find it.

An Excel workbook with the same name is uploaded to the SP Library once per week.

Don't I need a unique ID in the Excel workbook (in this case a column called 'ID'), and the Update function in Automate in order to update data already in the SharePoint List?

This seems to be working.

Do you see any fatal flaws? Do I add a action to the 'No' side where the condition is false that creates a new item in the SP List?

Phineas_0-1660155320629.png

 

SebS
Impactful Individual
Impactful Individual

You should create an action if the record does not exist in the List and then create it. I think I don't know what Your main objective is, but if you don't want this flow to do anything when the condition is not met, just leave it as it is.


If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users to find it.

I just had a thought; There will be no direct edit of the workbook.

The workbook will be updated by being overwritten with a new copy the workbook with the same name once per month.

Will this action trigger this 'When an item or file is modified' flow?

Or, would the above be considered 'create' and I need to use a different trigger?

SebS
Impactful Individual
Impactful Individual

Hi @Phineas 

 

When an item or file is modified will be efficient enough to do what you looking for.

 

Regards


If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users to find it.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,033)