Solution to Merge Excel files( 300 files) present in sharepoint doc lib

[maheshkhisteLiv]

Hi
I have a scenario where daily around 300 Excel files are uploaded to SP doc lib. I need to extract data from each and put it into single Excel. Every Excel uses same template but # rows might vary some may have 5 rows some may have 10 rows. Also every Excel has 2 sheets so I need to read data from both sheets and merge into single Excel with 2 sheets.
One solution I could think is Ms Flow Excel connector but it needs static path so I will have to create 300 actions. Another approach could be graph API or azure function but I am looking for no coded solution ...
Any thoughts / ideas will be help .
I have heard about azure data lake not sure if it's helpful

[v-yamao-msft]

Hi @maheshkhisteLiv,

Do you want to extract properties for 300 files and put them into a single Excel file?

You said that every Excel has 2 sheets, my understanding that one sheet will be used to store the file properties, how about another one? Do they have the same format?

To merge into single Excel with 2 sheets, you will need to get rows from one sheets, then add rows into another table.

I have made a flow likes below, please check it for a reference.

 

Best regards,

Mabel

[maheshkhisteLiv]

I already have thought about this solution but it's cumbersome as I need to put 1 get rows action per Excel so in my case I will have have 300 action plus 300 supporting so total will be 600+ actions. Hence I was looking for solid , scalable option. Also to answer your other question it's the same Excel sheet template filled by 300 people daily and at end of the day I need to combine it into single Excel. One sheet has static data which needs to be read and other has dynamic i.e. sometime 5 rows sometimes 15 rows.
I'm not sure if other ETL solution I can use for this or code or graph api

[harinn]

have you managed to find solution to this issue?