Merge Excel files in sharepoint to an Azure SQL database

[dbocvan]

Hello,

I have a SharePoint folder with hundreds of excel files with the same columns. Every day I save a new file.

How can I prepare a flow to take the rows of an excel file (when it is added to the folder) and append it to an existing Azure SQL table?

Thanks

[v-bacao-msft · Accepted solution]

Hi @dbocvan ,

Generally speaking, we can only get the created Excel table and configure it in List rows present in a table, so that we can get the field value in the Dynamic content list so that we can configure other actions.

Now we can directly use the Excel file identifier to get the field value in the Excel table. But it seems that the requirements are more demanding. https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#known-issues-and-limitations

If the Excel table is obtained dynamically, it is hoped that the structure of the fields in the Excel table is the same, and the table names also need to be the same. In this way, we can click Enter custom value to dynamically get the Excel table. Only when the above conditions are met, we can configure the same schema in Parse JSON to parse the data in Excel table.

You can refer to the following link for more details about Parse JSON:

http://johnliu.net/blog/2018/6/a-thesis-on-the-parse-json-action-in-microsoft-flow

Image reference:

Hope that makes sense.

Best Regards,

[dbocvan]

Hi Barry,

Thank you for your reply.

I'm a Power Automate entry level user with no experience in Java Script.

I think my requirement has 3 parts:

Build an Azure SQL table with data from hundreds of excel files saved in SharePoint, each one with same columns.

Merge to that same table other hundreds of excel files with other columns. The two type of files has the date column as common field.

I have a Power Automate flow activated that saves 2 daily files I receive in my gmail account, in a SharePoint folder. The idea is to update the new SQL table with the new data on daily basis.

Can you please share some documentation that I can refer to complete the 3 tasks?

Thank you,

[dbocvan]

Hi @v-bacao-msft ,

Any idea of how can I create a SQL table in azure based on several excel files saved in Sharepoint, and update the table using Power Automate?

Thank you,