How to merge files sub folders wise

Hi Team,

I have a main folder , in which I can have multiple subfolders which contains multiple excel files.

I have to merge files present in respective sub folders in one file. Files present in sub folder can have multiple sheets then merging will be done on respective sheet basis.

Hence each sub folder has one output merged file.

I have a solution where we can merge all sub folder files into one master file but I want to merge files present in each subfolders respectively.

please help

Check this below link, @Karan28

Hope this may help you :slight_smile:

Hi @Karan28

You need to get the path of all excel file first.
Use this to get the path of all files -

Directory.GetFiles(mainFolderPath, "*.xlsx", SearchOption.AllDirectories)

Rest is just reading the files and merging them

Hi , I dont want to merge all files into one file.

I want to merge each subfolder files into one. that number of subfolders = no. of files

This will give me all the files in an array . will not work in my case

Just get the path of all subfolders:

subFolderArr = Directory.GetDirectories(MainFolderPath)

iterate through the array of subfolders and get the files inside the subfolders and merge them.

to iterate use - for each item in subFolderArr


Hi @Karan28

do all the sheets in excel had to be merged ?

I got this , but there are different sheets present in the file and they have to merge sheet by sheet basis.

Not All file sheets data in one sheet . respective sheet data should be added together.

Yes, all file present in one sub folder will get merge into one file .

@Karan28 - Is it possible to share a screenshot of files in one folder and a spreadsheet showing sheet names?

1 Like

Correct me if I am wrong.

There is one folder say Main folder.
There are few sub-folders inside the Main folder like sub_folder1, sub_folder_2…
Each subfolder contains some excel files
Each excel file contains multiple sheets.
All the files in one sub-folder are to be merged into one in such a way that sheet1 of excel file 1 is to be merged with sheet 1 of excel file 2 and so on…

Please Confirm.

Yes yes , so there will be one output merged file in each sub folder

Yes I can

Sheet 2 of excel 1 will be merged with sheet2 of excel file 2

@Karan28 - Please check this… (77.9 KB)

Individual Outputs created per SubFolders…and the sheets also merged…


Please review and let me know…

How you are getting the Fileidx?

@Karan28 - It’s here…

Did you tested for your case and is it working??

Thankyou so much for the amazing solution

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.