Add columns from one datatable and merge with second datatable based on condition

Help Activities
, ,
1

Hi Folks,

I have two excel files, Master and Work. I want to check whether the column “MasterNumber” in MasterFile.xlsx matches with the “WorkNumber” column in WorkFile.xlsx. If the columns match, write the specific row of data from the Master file into the Work File. In case the column doesn’t match/duplicate values, print ‘0’ in front of those columns.

Please help me with a solved XAML file for the above scenario.

Thanks in advance.

Master File.xlsx (11.8 KB)
Work File.xlsx (12.1 KB)

Sample Output Required:
image

I tried this using Write Cell activity and it worked perfectly. But due to 1000 rows, can’t proceed with writing cell currently, thus need another solution.
My Solution:
MergeDT.xaml (23.6 KB)

2

Hey @shikharno.7 ,

You can try the XAML attached below along with the input and output sheets

Xaml :-
MergeDataTable.xaml (8.6 KB)
Sheets :-
Work File.xlsx (19.9 KB)
Master File.xlsx (11.8 KB)

Output ScreenShot :-

image
image540×632 8.7 KB

3

@Quenton_Wayne_Rebello , could you please assist me as to how I can resolve this error.

image

4

Sorry for the confusion, could you input this linq query over there and let me know if any error arrises

The right side is,
(From rowWork In dtWork.AsEnumerable Group Join rowMaster In dtMaster.AsEnumerable On rowWork("WorkNumber") Equals rowMaster("MasterNumber") Into master = Group From row In master.DefaultIfEmpty() Select ra ={rowWork(0),rowWork(1),rowWork(2),rowWork(3),If(row Is Nothing, 0, row(0)),If(row Is Nothing, 0, row(1)),If(row Is Nothing, 0, row(2))} Select dtOutput.Rows.Add(ra)).copyToDataTable

Xaml :-
MergeDataTable.xaml (10.1 KB)

Explaination of the code:-

  1. We read work excel sheet and assign it in a variable dtWork. Similar is done for Master excel sheet and stored in dtMaster.

  2. Then we take each row from the datatables where rowWork is from dtWork and rowMaster is from dtMaster, both the tables are joined based on the condition that workNumber is equal to masterNumber.

  3. The type of join is left outer join where we get all data of dtwork and matched columns of dtMaster. In the above code outer join is reffered to as Group Join and we group the joined contents into a group known as Master

  4. From each row in the group master we select the columns of dtMaster and checking if its nothing, If the content is null or nothing it is replaced by 0, else it takes the matched content from dtMaster. To this we add dtWork columns

  5. Finally the data selected is added row wise into the output datatable - dtOutput

5

What you’re trying to do is called a join, it’s a standard database operation. Use the Join Data Table activity. After that, whichever rows have blank values, you can update to 0 with a For Each Row in Datatable or LINQ.

6

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