Merge - excel data

Help
, , ,
1

Hi guys,

I want to consolidate multiple excel - sheet data into one excel.
example -
abc.xls - sheet 3 - need to read.
def.xls - sheet 1 - need to read.
xyz.xls - sheet2 - need to read.

output.xls should have all consolidated data available.

Please help.

1 Like
2

Hi
Did we try with MERGE DATATABLE activity
Like use read range for each sheet and get the output like dt1,dt3,dt3

—So first use a merge datatable activity and mention like this in source as dt1 and destination ad dt2
So all the data is now merged in dt2

—Then a final merge datatable activity with source as dt2 and destination as dt3

—Now dt3 is our data me having all dat merged together

—Now use a WRITE RANGE ACTIVITY and mention the input as dt3

Also ensure that all the sheet from excel have same set of table structure, same order of columnname, same number of columns

Cheers @Abhishek_Kumar_sinha

1 Like
3
4

Hi @Abhishek_Kumar_sinha,these Excel are Same column format ah?

5

Yes, these are of same table structure.

6

Thanks @Palaniyappan.
yeah right this way can be achievable :slight_smile:

But how to keep in loop if we have multiple excel and from different sheet name we need data.

1 Like
7

Let’s say like we have aground five sheets or workbooks and all has to be merged
—now we shall create two datatable variables named dt and finaldt with default value as New System.Data.Datatable defines in the variable panel

—now use a ASSIGN activity like this
arr_files = Directory.GetFiles(“Yourfolderpath”,”*.xlsx”)
Where arr_files is a variable of type array of string

— for low take one excel file path like arr_files(0).ToString and pass that as input to excel application scope and use read range and get the output with a variable of type datatable named Finaldt
—then use a CLEAR DATATABLE activity and mention as Finaldt and this is done to get the table structure

—now use a FOR EACH act and pass the above variable arr_files as input and change the type argument as string
—inside the loop use a EXCEL APPLICATION SCOPE AND pass the file path as item.ToString
—inside that scope use a READ RANGE activity and get the output with a variable of type datatable named dt
—then use a MERGE DATATABLE ACTIVITY and mention the source as dt and destination As Finaldt
—followed by this use a CLEAR DATATABLE activity and mention as dt

So that for each iteration of excel files data will be first fetched with dt and then merged to Finaldt
As dt is now cleared for each iteration new data from excel will be obtained and as Finaldt is not deleted it will keep all the data merged together

Atlast we can use write range activity and mention the input as Finaldt

Cheers @Abhishek_Kumar_sinha

3 Likes
8

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