I have two excel files in which 6 columns present in first and 8 columns present in 2nd excel.
In my output excel i need to copy the some column values present in first excel and second excel to this Data Table.
Any has Suggestions using Linq.
Thank you
Hi @mettudp075 ,
I think Linq can ok.
but you can use activity
Then follow the steps
1.First add the required columns to first datatable say dt1
2.Use for each row in datatable activity and in the properties add a variable for index
3.Use assign activity with currentrow(“ColumnName”) = dt2.Rows(index)(“ColumnNmae”).ToString
Repeat step 3 for each column you want to add
at the end of loop dt1 will have all the data from dt2 appended beside the data already present in dt1
same with file2
regards,
LNV
(From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable()
On row1(“CommonColumn”) Equals row2(“CommonColumn”)
Select dtOutput.Rows.Add(row1(“Column1”),row1(“Column2”),row1(“Column3”),
row2(“Column4”), row2(“Column5”), row2(“Column6”), row2(“Column7”), row2(“Column8”))).CopyToDataTable()
Hope it helps…
(From row In dt_plantDetails.AsEnumerable
Let colPlants = (From name In row.Table.Columns.Cast(Of System.Data.DataColumn) Select name.ColumnName).ToArray
Let colArray = (From name In dt.Columns.Cast(Of System.Data.DataColumn) Select name.ColumnName).ToArray
Where colPlants.Intersect(colArray) IsNot Nothing
Let rowArray = (From name In dt.Columns.Cast(Of System.Data.DataColumn) Let value= If(colPlants.Contains(name.ColumnName),row(name.ColumnName),Nothing) Select value).ToArray
Select dt.Clone.Rows.Add(rowArray)).CopyToDataTable
Here dt has 8 columns and dt_plantDetails has 6 columns. If the column names are similar, it will add the values to dt from dt_plantDetails
These are two Different Tables for example first one is Employee I’d Employee salary.
Second one is location,Branch.
In third Table there is four column headers with emp I’d, employees info location info we need to copy values from table1, table 2 to table 3
Maybe you could provide a data table like this one? And Identify there are columns to match?
Like what will be identifier in merging the two data tables.
So I came up with this solution.
Assign dtOutput = (
From employee In dtEmployee
Join branch In dtBranch
On employee("Employee ID").ToString.Trim Equals branch("Employee ID").ToString.Trim
Let EmployeeIDColumn = employee("Employee ID").ToString
Let EmployeeNameColumn = employee("Employee Name").ToString
Let SalaryColumn = employee("Salary").ToString
Let BranchColumn = branch("Branch").ToString
Let LocationColumn = branch("Location").ToString
Select dtOutput.Rows.Add(EmployeeIDColumn, EmployeeNameColumn, SalaryColumn, BranchColumn, LocationColumn)
).CopyToDataTable
Here is sample process file:
Compare Two Data Tables.zip (3.4 KB)
Let me know if this works or if you find another solution.
Do you mean that there is a common column between the two and you want to match them up, like if you had Employee Name and State Abbreviation in one table, and State Abbreviation and State Name in another table, and want one table with Employee Name, State Abbreviation, and State Name?
If so, this is called a join. Use the Join Data Table activity.
Both Tables have same columns names we need to paste the columns values in a new table like Employee1, Location1 from table1 and Employee2, Location 2 from table1 and compare these two values same.
If you want to compare them, you don’t need to combine the datatables. Search the forum there are lots of posts on how to compare two datatables.
If they both have Employee and Location, and you just want all the rows in one table with Employee and Location, then use Merge Data Table.
I need to compare the values after running SQL query I got an output from the output i need fetch required column and place in a output excel and From input excel i need to fetch required column and place in output excel file
Note: Both have same columns but some times values will differ if values differ i need to update y in Attribute A otherwise N
Show us the data. Show us what you want the output to look like.
Anil_G
jast1631
yuichi