Consolidate Multiple Excel files into one excel

Help Studio
, ,
1

Hi,

How to consolidate the multiple excel files which is in one folder into one excel
Help me out

2

Hi,

Just to confirm do you want to consolidate every excel data in one excel sheet or in different sheets of one file

3

Hi,

Yes I Consolidate every excel in one excel in one sheet.

4

Lets assume all excels do have the same data column structure and consolidation is meant as merge all files into a single worksheet

find all excelfile:
arrFiles = Directory.GetFiles(“YourFolederPath”, “*.xlsx”)

then loop over all files. Write the first excel with a write range to an excel into a new worksheet. Afterwards use append range for all other excel files and add it.

5

Adding to the peter solution,

Just also check the columns format , and the format of data u need, you might need to do some editing after appending the data ( if needed).

6

Hi,

Now I Am Working Like This Only. But It Takes A long time to complete the process, because the excel volume is huge.
Is there any other option to resolve this problem?

7

give a try at:

  • merging on data table level with the merge datatable activity
  • write range of the final datatable to excel

When performance is still not in expected range then just open a new topic bring the performance into the focus of the new topic

8

All the excel columns are same
Want to consolidate all excel file into single excel(with single sheet)
All excel volume is huge, Took more than half an hour to run when using for each directory

9

Hi @sharmila.ar ,

If u have a same columns on both, then use MergeDataTable Activity, and set Source from second datatable and Destination to first datatable. And combine it in For Each Activity for every excel files u have.

10

is it possible for you to share one excel file , if not getting uploaded maybe some share drive link though which we can get one sample file.