Linq For 2 datatables. Inserting datat from one datatable to another using one common column

Help Studio
, ,
1

Hi Team,

I have 2 datatables,

1st datatable:
image
It contains State’s Name.

2nd datatable:

image
image210×534 3.86 KB

It Contains Statename and Price Group.
I want the Price group in the 1st datatable.

I can do it using Vlookup but i want the solution using LINQ.

Please help team.

2

Hi @yash.choursia ,

Maybe could you try using Join Datatables activity with the State and Price Group Desc columns as the columns to Match. Keep the Join Type as Left Join with First Input datatable being the 1st Datatables shwon.

As this should be a simpler configuration we are suggesting this.

Let us know if this doesn’t work for your case.

3

dtFirst.AsEnumerable().ToList().ForEach(Sub(row) row(“ColumnToBeUpdated”) = dtSecond.AsEnumerable().FirstOrDefault(Function(r) r(“ID”) = row(“ID”))(“CorrespondingColumn”))

4

Hi @yash.choursia,

Assume your datatable (having column state) is dt1 and other datatable is mappingDT:

By using below LINQ, you will have a datatable with 2 columns state name and mat. price grp, those present in dt1.

mappingDT.AsEnumerable().Where(Function(row) dt1.AsEnumerable().Any(Function(x) x(“state”).ToString=row(“price group desc”).ToString)).CopyToDatatable

5

You can try using,

(From row1 in dt1.AsEnumerable
Join row2 in dt2.AsEnumerable On row1("State") Equals row2("Price Group Desc")
Select dt2.Clone.Rows.Add({row1("State"),row2("Mat. Price Grp")})).CopyToDataTable
6

image

This is the error. Please help

7

i have a column in dt1, i want to insert data in that column only. @Quenton_Wayne_Rebello

8

Hey, in the code

(From row1 in dt1.AsEnumerable
Join row2 in dt2.AsEnumerable On row1("State") Equals row2("Price Group Desc")
Select dt2.Clone.Rows.Add({row1("State"),row2("Mat. Price Grp")})).CopyToDataTable

It will select column from dt1 and its respective Price Grp from sheet2 into a new datatable. Using this you can rewrite it into sheet 1.

If you want only the Mat Price Grp you can do the below code,

(From row1 in dt1.AsEnumerable
Join row2 in dt2.AsEnumerable On row1("State") Equals row2("Price Group Desc")
Select row2("Mat. Price Grp")).ToArray
9

@Quenton_Wayne_Rebello

I want to check a column “state” in both the datatables. then, add data from dt2 to a specific column in dt1. Is it clear?

10

Hey, Can you share the sheets you are using for a better query

11

Let me explain, I have dt1, whhich has a total of 30-35 Columns.
This dt1 Contains one column “State” and Another column “State Code”(which is empty needs to be filled from dt2).(These 2 columns are included in that 30-35 columns).

Now,
I have another datatable dt2,
It has only 2 columns, “State” and “State Code”. Both has values. Now we have to match “State” columns in both the datatables and fill the respective codes that are present in dt2 in dt1’s “State Code” column.

I hope it is clear now.

12

Hey @yash.choursia ,

Your requirement will be resolved if you implement the LINQ expression provided by @Quenton_Wayne_Rebello

13 
(From row1 In dt1.AsEnumerable
Group Join row2 In dt2.AsEnumerable On row1("State") Equals row2("Price Group Desc") Into Group
From grp In Group 
Select dt1.Clone.Rows.Add({row1("State"),If(grp Is Nothing,Nothing,grp("Mat. Price Grp"))})).CopyToDataTable

As per the above query,

  1. It will Join the sheets based on the states
  2. If the state is present it will provide the Mat. Price Grp value from sheet 2
  3. If the state is not present, it will give empty string
  4. This will be added to a output datatable that can be rewritten to the first excel sheet
14

okay, i will get a new datatable Right?
I cannot get it in the same dt1. Corrrect?

15

You can either assign it to dt1 or any other DataTable variable.

16

Yes that is right @yash.choursia

17

pass the arguments in invoke code

18

already passed
but showing the same error

19

did you checked variable type

20

Use the below method

IMG-20230817-WA0000
IMG-20230817-WA0000800×282 23.4 KB