I want to add some specific column in different excel sheet.I have an excel file which has sheet1 and sheet2. sheet1 is having columnA, columnB and sheet2 is having columnC, columnD,columnE. I want columns A,B,C,D,E into single sheet.
You can use linq expression to merge the the columns in two excel sheets.
Check the below steps,
→ Use the Read range workbook activity to read the Excel Sheet1 and store in a datatable called dtSheet1.
→ Use the another read range workbook activity to read the excel sheet2 and store in a datatable called dtSheet2.
→ then use the build datatable activity and create the 5 columns in it and create a variable in the output called dtOutput.
→ Then use the assign activity and give the below linq expression,
- Assign -> dtOutput = (From r1 In dtSheet1.AsEnumerable(),
r2 In dtSheet2.AsEnumerable()
Select dtOutput.Rows.Add(
r1("ColumnA"),
r1("ColumnB"),
r2("ColumnC"),
r2("ColumnD"),
r2("ColumnE")
)).CopyToDataTable()
→ Then use the write range workbook activity to write the dtOutput to the other sheet.
@mkankatala Input is not fixed here…As i mentioned earlier file1(input 1) can contain n number of column and i need to fetch 3 specific column from file2(input2) and add these fetch column in file1.So if file 1 is contain 13 column then need to add those 3 column from 14th onwards and if input 1 is contain 6 column then need to add file 2(fetched column) from 7th column.
Follow the below steps
Build Data table - FinalDT, ColumnA, ColumnB…
Read range for both sheet, sheet1, sheet2 store the both data in variable
Take assign activity - To AaryofDatarow- assign sheet 2 data
Call for each row data table activity- pass sheet1 data
then take add data row activity pass data whatever you pattern
below the for each row data table take write range activity and pass the data that you added recently
You have to use the Vb code in Invoke code to achieve it.
Follow the below steps,
→ Use the Read range workbook activity to read the Input Excel 1 and store in a datatable called DT1.
→ Then use another Read range workbook activity to read the Input Excel 2 and store in a datatable called DT2.
→ Then use the Invoke code activity, open the Edit code and paste all the below code,
' Ensure the columns exist in DT1 before adding the data
If Not DT1.Columns.Contains("ColumnC") Then
DT1.Columns.Add("ColumnC", GetType(String))
End If
If Not DT1.Columns.Contains("ColumnD") Then
DT1.Columns.Add("ColumnD", GetType(String))
End If
If Not DT1.Columns.Contains("ColumnE") Then
DT1.Columns.Add("ColumnE", GetType(String))
End If
' Add data from ColumnC, ColumnD, ColumnE of DT2 to DT1
For i As Integer = 0 To Math.Min(DT1.Rows.Count, DT2.Rows.Count) - 1
DT1.Rows(i)("ColumnC") = DT2.Rows(i)("ColumnC")
DT1.Rows(i)("ColumnD") = DT2.Rows(i)("ColumnD")
DT1.Rows(i)("ColumnE") = DT2.Rows(i)("ColumnE")
Next
→ Open the Edit arguments option and pass DT1 with In/Out direction and DT2 with In direction.
→ Then use the Write range workbook activity to write the DT1 to the Same Input Excel 1.
Note: Change the Column names in the vb code as per your input data.