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

[PJBruen]

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:

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

Many thanks all..

Paul.

[PJBruen]

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?

[v-litu-msft]

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]

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

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

Many thanks...

Paul

[v-litu-msft]

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]

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"}]}}

[PJBruen]

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 · Accepted solution]

The answer to this can be found here: https://flowaltdelete.ca/2020/06/25/power-automate-excel-dynamic-file/