Combine Multiple Excel files data into a single sheet

Help Studio
, , , , , , ,
1

Hi Everyone,

Require your help on a use case.

I need to merge data from excel files which are nearly 5000+ files into a single excel file. The data in each file is not that much huge. I am currently looping over a list of files, retrieving the information and merging data using merge data table activity, but this whole operation is taking a lotttt of time which is breaching SLA of process.

Can anyone please help me with some other better efficient approaches to merge data into a single file from multiple files, I know there is a way by doing with Vb script, interop, C Sharp but i am not that proficient in them.

Any help would be great.

Thanks!

2

@Dhruvi_Arumugam

Welcome to our UiPath community.

The procedure you are following is the correct one and we don’t have any other activities to do this task quickly. Hope you are not writing one by one file data into Excel file.

If you are doing that way please avoid that way and write all the data once at the end of reading all files completed.

3

Hi @lakshman,

Yes, i am using merge datatable activity to merge data at once for all then writing but the processing is becoming too slow.

Thanks

4

Hi @ppr @supermanPunch Can you guys help here?

5

Hi @Yoichi @Gokul001 Can you guys also have a look

6

Hi @Dhruvi_Arumugam ,

Are you using Excel Application Scope or WorkBook Activities ?

7

Files are xlsx and Csv as well so using excel application scope to read.

8

@Dhruvi_Arumugam , Maybe you can Try using Workbook Read Range and Read CSV Activity and Check the Process Efficiency.

To Understand which Activity to use we can use a Switch case like below :

image

To get the Extension of the file and understand if it is either a csv or xlsx we use the Below Method :

Path.GetExtension(YourFileNameVar)

Then Use Read CSV and Read Range Activities in their Respective cases.

9

Will using Workbook read range activity and read csv activity make any difference in your views?

10

@Dhruvi_Arumugam , Reading CSV files using Excel Scope as we have seen in some posts may cause some formatting issues (Not Entirely sure).

Also, Using Excel Application Scope Every time creates Excel Instance every time, which may result in Slower Execution.

But once you get to Test the Alternate we should be able to Check the Efficiency and Understand if it’s really the case. Since as you mentioned there is no Huge Data in the files, these must be the reasons.