I have 100 excel documents with the same data structure and property fields, I want to merge all the excel documents into one excel document, how can I do it?

[491979546 (Vegeta)]

Has anyone done a scene like this, please?

[rahulsharma]

Consolidation can be done using VBA

Data Extraction from multiple Excel files

As the files are in huge numbers, you can also have the consolidated file or data using vba Just pass the argument doe thr folder path. Sub ConslidateWorkbooks() Dim FolderPath As String Dim Filename As String Dim Sheet As Worksheet Application.ScreenUpdating = False FolderPath = Environ("userprofile") Filename = Dir(FolderPath & "*.xlsx*") Do While Filename <> "" Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWor…

There’s an alternative way too.

https://www.codeproject.com/Tips/5264786/Merge-Many-Excel-Files-into-one-VBS

[491979546 (Vegeta)]

@rahulsharma

Consolidation can be done using VBA

Thank you，bro. But ,I’m not familiar with VBA，how do I put these VBA code into the UiPath Activity？ Could you please show me how to do it？

[Gopal_Tewari1 (Gopal Tewari)]

Hi

First Loop to all your Excel Sheets and then Inside Loop read each excel sheet data and store it in a DataTable (datatable1)

Write an If Condition that your Maindt.Rows.Count>0 then Use Merge Data Table activity and Pass Source as datatable1 and Destination as Maindt. else Store datatable1 all the data inside Maindt by using Assign Activity (Maindt=datatable1)

@491979546

Has anyone done a scene like this, please?

@491979546

[Palaniyappan (Forum Leader)]

Hi

Please refer this thread for a xaml

How to combine multiple excel files into single one-Advance training help

Hi All, how to combine multiple excel files into single file and save it with proper name. Please help… Regards, SagarSB.

And if you are looking for a component

Merge Content of Multiple Excel Files - RPA Component | UiPath Marketplace |...

Cheers @491979546

[491979546 (Vegeta)]

@Gopal_Tewari1

First Loop to all your Excel Sheets and then Inside Loop read each excel sheet data and store it in a DataTable ( datatable1 )

Write an If Condition that your Maindt.Rows.Count>0 then Use Merge Data Table activity and Pass Source as datatable1 and Destination as Maindt. else Store datatable1 all the data inside Maindt by using Assign Activity (Maindt=datatable1)

Thank you，Bro. Do you have more detailed instructions on how to do this? Actually , every excel file have only one sheet. I know how to loop the files , but don’t know how to merge each DataTable from each excel file…

[Gopal_Tewari1 (Gopal Tewari)]

Hi

Please find the attached screenshot of the solution
Here first we have a loop to our Excel Doc and then read one by one each excel first sheet data and on the basis of Condition we have merged our data in the main data table

Happy Automation

[491979546 (Vegeta)]

it is too hard for me . Can you show me a Demo.

[Gopal_Tewari1 (Gopal Tewari)]

Hi
Please find the below working Demo
TestExcel_Demo.zip (14.6 KB)

Cheers
@491979546

[491979546 (Vegeta)]

Thank you so so much。。
Your demo can achieve the effect I want, but I follow your demo and it says maindt is empty and reports an error.

[Gopal_Tewari1 (Gopal Tewari)]

Hi @491979546

Please use the Updated one I have attached after a few modifications and make sure you have initialized the maindt with New DataTable() in variable panel , if you are still facing the issued share the error screenshot

Thanks

[491979546 (Vegeta)]

Bro，please help me with it。

[Gopal_Tewari1 (Gopal Tewari)]

Hi @491979546

This error has occurred because you haven’t initialized the mainDT variable with New DataTable as Default.

See the below-attached screenshot

Cheers

[491979546 (Vegeta)]

Great, thank you so much, I’m a newbie , so I aways make mistakes…is new DataTable（）a method which means create a empty DT？

[Gopal_Tewari1 (Gopal Tewari)]

Hi @491979546

No Issue we are happy to help

Exactly it means that you are initializing the DataTable with Empty DataTable before going to use it.

Thanks

[491979546 (Vegeta)]

@Gopal_Tewari1

No Issue we are happy to help

Exactly it means that you are initializing the DataTable with Empty DataTable before going to use it.

Thanks

Thank you ，you are great UiPath master！