Unanswered

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

(0) Share

Report

Posted on by 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

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

v-yamao-msft on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
maheshkhisteLiv 8 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Copy link

Link copied!

have you managed to find solution to this issue?

Was this reply helpful? Yes No
Paul16 2 on at

Like (0)

Report
Copy link

Link copied!

Hi @maheshkhisteLiv,
Tell me please, you found the solution.
I have the same situation and I can't find any solution to solve this problem.
Thanks in advence
Paul

Was this reply helpful? Yes No
Vaibhav_k on at

Like (0)

Report
Copy link

Link copied!

Hello @maheshkhisteLi, I am in to similar situation. How did you ever resolve this?

Was this reply helpful? Yes No