cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbocvan
New Member

Merge Excel files in sharepoint to an Azure SQL database

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

 

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:

109.PNG110.PNG

 

Hope that makes sense.

 

Best Regards,

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

View solution in original post

3 REPLIES 3
v-bacao-msft
Community Support
Community Support

 

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:

109.PNG110.PNG

 

Hope that makes sense.

 

Best Regards,

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

View solution in original post

dbocvan
New Member

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
New Member

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,

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (71,054)