How to merge mulitple fexcel files with unique columns in all the excel files

Help Studio
, , , ,
1

Hi Team,
I need merge and union of all records from multiple excel file placed in folder. Like below is ex of the data.

file1.xlsx

Name email year ActivityCode Count
ABC abc@test.com 2023 01 5

File2.xlsx
Name email year ActivityCode Count day
ABC abc@test.com 2023 02 7 3
xyz xyz@test.com 2023 02 6
dfg dfg@test.com 2023 02 1

File3.xlsx
Name email year ActivityCode Count day
ABC abc@test.com 2023 02 2 2
xyz xyz@test.com 2023 02 4 2

Final output

Name email year ActivityCode countfile1 Countfile2 countfile3
ABC abc@test.com 2023 01 5 7 2
xyz xyz@test.com 2023 02 0 6 4
dfg dfg@test.com 2023 02 0 1 0

if possible, could someone please provide me the linq query for this

2

Read all the Excels and Join the datatable. After that remove the additional Name,email, year, ActivityCode columns from final datatable.

3

its bulk data…planning to use the linq query…