Dynamic table for sharepoint excel

[suelahmed]

I does some help or advice. I explain the business case.

1. I get a report in excel format in my mail box on a daily basis.
2. Automatically create the table for the sheet
3. Once the table is created, I have to merge this excel sheet with another excel sheet based on a primary key.
4. After merging these two excel sheets upload the data in a sharepoint list. (create new key or update the old data).
5. Repeat the process again the next day.

Any help woule be appreciated

[suelahmed]

FYI, I am stuck at point # 2, dynamic table creation in excel. Rest part of teh flow is ready and working.

[v-duann-msft]

Hi @suelahmed 

Thanks for posting.

Could you provide more details about the Email body? Does it have a fixed format?

If there is a fixed format, we could be able to use function to extract the specified data.

Then store the data in the Excel table, you may need to use Add a row into a table action.

Here is the sample of flow:

Here are some similar threads, please refer to the method inside and see if it helps: 

https://powerusers.microsoft.com/t5/Building-Flows/Extracting-text-from-email/td-p/334919

https://powerusers.microsoft.com/t5/Building-Flows/Extract-text-from-body-of-an-email-and-create-new...

If you need more assistance, please provide more details so that we can provide the appropriate workaround.

Thanks

Anna

[suelahmed]

Hi @v-duann-msft 

Yes the file has a fixed format.

An example of structure of excel data is :

ID | Business Name | Manager Name | Date of Submission | Manage Email

B01  Finance               XYZ                     30/11/1990                XYZ@Corp.com

Instead of creating the table, can I directly update the sharepoint list on a daily basis, using some iterations of above sample flows?

[suelahmed]

@v-duann-msft 

Just to be clear, the report is an excel attachment. I guess I didn't clarified that point properly.

Regards,

Suel

[v-duann-msft · Accepted solution]

Hi @suelahmed 

Thank you so much for further valuable information.

If that’s the case, we need to change flow as below:

After running flow, I’m able to get excel with table:

Hope the content above may help you.

Thanks

Anna

[suelahmed]

Hi Anna,

So whenever an updated file arrives in the mail, Will the flow update the existing excel table?

Could you please expand the GetfileMetadata and GetfileProperties step and reshare the screenshot?

[v-duann-msft]

Hi @suelahmed 

Thank you for your reply.

Here you are:

So whenever an updated file arrives in the mail, Will the flow update the existing excel table?

No, the flow is used to copy attachment to SPO and create excel for it. It means, the flow will create new excel and table for each received attachment. If you want to update info to existing excel, we need to create another flow to focus on ‘add a row into a table ’ action to add new record to existing excel.

Best regards,

Anna