Adding columns from multiple excel sheets

Hello everyone, I am trying to add the column from multiple excel sheets into one excel sheet. I tried with append range activity, but the columns are overwriting. I want all the columns from different excel sheets should be in the same excel sheet.
Please help me to solve this.
Thanks in advance!

You have multiple excels rights like 1,2,3.4
All 4 excel should come in one excel then,
Using read range activity read all the excels.
To 1st excel give the write range activity
for next 2,3,4 excels give append range activity
Hope it works

–hope we have the datatable obtained from all different sheets
–create a variable inthe variable panel named Finaldt of type System.Data.Datatable and with default value as New System.Data.Datatable
–next to this use a simple assign activity to get the data column from one of the sheets we have
like this
dtcol1 = Datatable1.Columns(“yourcolumnname”)
where dtcol1 is a variable of type System.Data.Datacolumn
–now once after creating a variable now we can use ADD DATA COLUMN activity where in the property panel mention dtcol1 as input to column and in the datatable mention as Finaldt
–so that the new obtained column will get added to the Finaldt Datatable
–likewise we can get the data column from the all the different sheets and add them to the Finaldt

hope this would help you
Cheers @suma123

Hi @sudhasagar, Thanks for your quick reply. I followed the steps you mentioned but in append range activity, the data is writing in new row but i want it in new column (as last column) in same excel sheet.

Thanks for your reply @Palaniyappan, I am getting error in add data column

1 Like

Is Finaldt variable defined in the variable panel with default value as New System.Data.Datatable

cheers @suma123

Yes @Palaniyappan I tried with that but again getting an error

1 Like

Hi @suma123

Check this

Use read range->use assign activity DtTble.Columns(“Column Name”).columnName=“new column name”

Use write range


Hello @AshwinS2, Thanks for your reply. Please the below image

Hi @suma123

Use this

DtTble.Columns(“Date”).ColumnName on lhs and “textvalue” on rhs


hmm.if possible can i have a view on the xaml and the excel files (sample files is enough with dummy data)

Sure @PalaniyappanFINAL.xls (25.5 KB)
This is my final output excel sheet. Every day i need to add the column (In Duration (In Hrs))in my final excel sheet from different excel sheet
Monthly1.xaml (9.9 KB)
Please help me
Thanks in advance (26.2 KB)

Dear Suma Please find the Attached Solution for your Requirement.

Thank you so much @sunil_singh1. It’s working

Your Welcome @suma123

Hello @sunil_singh1
If my Data Table consists of 10 elements(data) and if I want to retrieve 1st element, What query can I use.
For example I want to write the data in columnD. Please go through the following sheet
Final out.xlsx (8.7 KB)

I’m Sorry @suma123,

Can you please little bit elaborate the Scenario if possible Attache the XML Sample so that i can provide you the solution if possible.

Try this DataTable.Rows(0)(0).ToString which will give you the 1 element or

Final out.xlsx (9.0 KB) Samplesheet.xlsx (8.3 KB) Select_element.xaml (7.0 KB)

Dear @suma123.
Please find the Solution

Select_element.xaml (10.6 KB)

Dear @suma123 One more Solution.