cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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
3 REPLIES 3
Community Support
Community Support

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.

1.PNG2.PNG 

 

Best regards,

Mabel

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

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

have you managed to find solution to this issue?

Helpful resources

Announcements
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (91,396)