i am trying to join two datatables with few columns same , before joining the two tables , i remove those common columns leaving only the ID , but after joining them when i write them to excel , the columns that are common have their data coming from different table pasted in seperate rows.
which activities have you used?
join data table or merge data table can be used here in your case
I tried merge data table but it gave the same results , so i tried joins now but results remain the same
dtA = dtA.AsEnumerable.Zip(dtB.AsEnumerable,Function(r1,r2) dtA.Clone.LoadDataRow(r1.ItemArray.Zip(r2.ItemArray,Function(v1,v2) if(String.IsNullOrEmpty(v1.ToString),v2,v1)).ToArray,False)).CopyToDataTable
Hope it helps
Its not joining the tables properly ,wait i will send you the excel sheets
What column are you joining on and what type of join are you doing?
If you perform inner join only then it will get the common rows …if that is what is needed
Also what columns are you joining on?
Also if one table has data without spaces and other with spaces then also they might not join properly
merge2datataable.zip (8.5 KB)
i have tried merge data table activity and it worked for me
once check this code and let me know
I am joining on id column , and one table has few empty cells
Actually i have two datatables , and one of them has few empty cells
I just want the first datatable to have columns added from second datatable,
I am using id column which is common for both tables.
Do a left join so it only takes matching columns from the right side table.
Read Range Table1.xlsx into DT_One
Read Range Table2.xlsx into DT_Two
Join Data Tables activity
- Input DataTable 1: DT_One
- Input DataTable 2: DT_Two
- Join Type Left
- Column Table 1: “Emp Id”
- Operation: =
- Column Table 2: “ID”
- Output DataTable - this can be a new datatable such as DT_Final or you can just use DT_One and it will be overwritten
Then which ever table output of two in which you need all rows even if matched or not take that table as dt1 and other datatable as dt2 …now perform a left join eith dt1 as first table
And if some columns are not needed after join use a filter datatable activity to remove those columns or retain only columns which you need
I did a left join but i am getting this
Data.xlsx (10.6 KB)
Table 2 data is not getting populated
Looks like the given columns are wrong or the tables are having slightly different data
Something is different about the formatting of ID and Emp Id so they aren’t matching. Checking PreserveFormat for the Read Range (workbook) Table1.xlsx fixes it.
Emp Id , i took from an excel using Read Range which had it has Object type.
ID , i took from build data table , i kept its datatype as Object type.
Did i do something wrong ?
Don’t use Build Datatable before reading the files. The Read Range creates the datatable for you.
But that doesn’t matter because whatever you did in Build Datatable just gets overwritten by the Read Range.
There is no reason Emp Id would be Object.
Use the WORKBOOK Read Range activity to read the files, not the EXCEL Read Range. In the Read Range that reads Table1.xlsx just check the PreserveFormat box. Then the values will match between the two tables.
Or figure out how the format of the columns is set in both Excel files and why it’s different, and fix that difference.
Table1(Emp Id) is from read range workbook and Table 2(ID) is forming in runtime.
For Table 2 i used build table.
I used this syntax " Datatable.Columns(“Emp Id”).Datatype " to check the datatype and found Emp Id was a object and ID was a string.
So for performing joins , i made ID column a object. So that both ID columns from both table will have same datatype