cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PJBruen
Resolver I
Resolver I

Build flow to extract file content and add to a Sharepoint list

Hi, 

I need to build a flow that does the following. Prior to this, I have a Powerapp that will know the file path of an Excel file that is held in the sharepoint Document Library. So, the flow needs to:

  1. Navigate to the Sharepoint document Library and the path passed through to the flow.
  2. Extract a named table from the file
  3. Copy those contents and populate an existing Sharepoint List

 

Can anyone help me with this please....I'm a bit stumped?

 

The Excel file will look like this:

PJBruen_0-1605694177887.png

The Sharepoint list will look like this (with the "StressTest" column value being derived from the PowerApp 

PJBruen_1-1605694792592.png

 

Many thanks all..

Paul.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PJBruen
Resolver I
Resolver I
7 REPLIES 7
PJBruen
Resolver I
Resolver I

Ok, so I now have a working flow for this. The only issue is getting the Dates from Excel to work. For now, I've had to switch the Sharepoint field for "Start Date" to be a string, but that means the date in Sharepoint is now a number (44075).

 

Can anyone advise how the flow needs to be changed in order to accept the Start Date as a date and populate Sharepoint as a date?

 

PJBruen_0-1605711309347.png

 

v-litu-msft
Community Support
Community Support

Hi @PJBruen,

 

The date column return of Excel table is a number of days count from the "1899-12-30", you could use expression to convert it, for example, you could copy this expression then paste it into the field:

addDays('1899-12-30',int(item()?['Start Date']),'yyyy-MM-dd')

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PJBruen
Resolver I
Resolver I

Hi @v-litu-msft

Thanks for your reply. We had tried this formula but I think a combination of regional settings, date formats etc etc seem to restrict this from working. Having said that, I did actually get it to work (and pull through a date from Excel to Sharepoint in US format), but as I'm in the UK, I needed to change it....and since then I've had no success.

 

Here's a bunch of screenshots as to what I have. If anyone can suggest where I'm going wrong that would be great. I've tried changing the Sharepoint field from date to string.....but still no luck (the screenshot is a manual entry to show this). It's currently set as a date and in US format (as I appear not to be able to change that at the moment).

PJBruen_0-1605781241839.png

 

PJBruen_1-1605781270514.png

PJBruen_2-1605781300628.png

PJBruen_3-1605781327065.png

addDays('1899-12-30',int(item()?['Start Date']),'yyyy-MM-dd')
 

PJBruen_4-1605781352286.png

 

Many thanks...

Paul

 

 

 

 

 

 

 

 

 

v-litu-msft
Community Support
Community Support

Hi @PJBruen,

 

Could you please share an instance of the List rows present in a table action?

I want to know the value and column name, thanks.

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PJBruen
Resolver I
Resolver I

Hi @v-litu-msft 

 

Thanks for your reply. Sorry, I'm not entirely sure what you mean, hopefully the following helps though. This is the detail behind the Click to Download button on the Excel Rows Present in a table object.

 

{"statusCode":200,"headers":{"Pragma":"no-cache","Vary":"Accept-Encoding","x-ms-request-id":"cbe71a8d-9fc6-4cb8-b413-b031766a6c7c;d52af850-4967-42fd-80a9-c31acae5d781;0f3411c7-c1ef-4213-b00e-2d639796f55c","OData-Version":"4.0","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","Cache-Control":"no-store, no-cache","Date":"Fri, 20 Nov 2020 10:04:14 GMT","Content-Type":"application/json; odata.metadata=minimal; odata.streaming=true","Expires":"-1","Content-Length":"2653"},"body":{"@odata.context":"https://excelonline-we.azconn-we.p.azurewebsites.net/$metadata#drives('b%21z34ydIOn5keYNFZ9AfVcLLqvSnvSfpBKojfNVv80Wzu4fuWYq4WURbALufxNlyqk')/Files('01HGDSC7MS35HIW3Z3KRA3B2CG76FUON2Q')/Tables('%7B0C547A32-FF9C-416F-A1D0-58D3D47CB4AA%7D')/items","value":[{"@odata.etag":"","ItemInternalId":"06e7bdfa-bc17-4812-ab59-9e3c4f10c7c0","Key Milestone ":"Milestone 1","Owner ":"Person A","Start Date ":"44087","Delivery Date/Time":"No Date","Status ":"Complete"},{"@odata.etag":"","ItemInternalId":"4d1fac99-9b59-4696-8db2-cab0f9ab5ae8","Key Milestone ":"Milestone 2","Owner ":"Person B","Start Date ":"44088","Delivery Date/Time":"No Date","Status ":"Delayed"},{"@odata.etag":"","ItemInternalId":"a8577a6e-256c-44b9-9449-9fea3a688095","Key Milestone ":"Milestone 3","Owner ":"Person C","Start Date ":"44089","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"46a714fa-1949-407d-b71e-b63e906262e1","Key Milestone ":"Milestone 4","Owner ":"Person D","Start Date ":"44090","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"9dbf064f-7233-4efb-86a4-2df8b6c4f21a","Key Milestone ":"Milestone 5","Owner ":"Person E","Start Date ":"44091","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"68f86257-85e6-46a5-aca5-c98a6e1847b6","Key Milestone ":"Milestone 6","Owner ":"Person F","Start Date ":"44092","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"63e1aba9-a5e6-4af1-833d-4059ea24a72e","Key Milestone ":"Milestone 7","Owner ":"Person G","Start Date ":"44093","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"ddcc746e-a022-4508-b33b-76ff64e580b6","Key Milestone ":"Milestone 8","Owner ":"Person H","Start Date ":"44094","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"2a2fcd92-0251-409c-9ae2-c660f6e58ba2","Key Milestone ":"Milestone 9","Owner ":"Person I","Start Date ":"44095","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"ff998d99-93be-44fc-9628-49073cc8d5af","Key Milestone ":"Milestone 10","Owner ":"Person J","Start Date ":"44096","Delivery Date/Time":"No Date","Status ":"On Track"},{"@odata.etag":"","ItemInternalId":"d6d96ba0-9a5a-4caa-91a5-816b5e9c9013","Key Milestone ":"Full Submission","Owner ":"Project Team","Start Date ":"44097","Delivery Date/Time":"No Date","Status ":"On Track"}]}}

 

Hi @v-litu-msft  - thanks for your reply.

I appear to have resolved the pulling through of the date!! I think I had trailing spaces in the spreadsheet column names.

 

I do however have another question. Currently the choosing of the Excel file requires it being selected and is effectively hard-coded.

Do you know of anyway I can do the same operation, but with the Excel File Path passed through from PowerApps as a variable. Essentially, this process needs to be run for any selected Excel file that sits in the Document Library.....but the data all gets fed through to the same Sharepoint list.

 

Hopefully that makes sense!!

Regards,

Paul

PJBruen
Resolver I
Resolver I

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.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,635)