How to merge files sub folders wise

Help Studio
, ,
1

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

2

Check this below link, @Karan28

Hope this may help you :slight_smile:

3

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

4

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

5

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

6

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

image

7

Hi @Karan28

do all the sheets in excel had to be merged ?

8

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.

9

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

10

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

1 Like
11

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.

12

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

13

Yes I can

14

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

15

@Karan28 - Please check this…
MergeSheets_PerSubFolders.zip (77.9 KB)

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

image
image

Please review and let me know…

16

How you are getting the Fileidx?

17

@Karan28 - It’s here…

image
image1349Ă—241 12.6 KB

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

18

Thankyou so much for the amazing solution

1 Like
19

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