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

did you checked variable type

Use the below method

is there any way by which i can add it in the same dt1.?

yes. there are 2 datatable arguements

image

See i tried this query and tried to change on that datatable itself. It is giving me this error. Can you help me with this?

Hey @yash.choursia,

Try this -

(From row1 In dt1.AsEnumerable
Group Join row2 In dt2.AsEnumerable On row1(0).ToString Equals row2(0).ToString Into Group
From grp In Group.DefaultIfEmpty()
Select ra = {row1(0),If(grp Is Nothing,Nothing,grp(1))}
Select dt1.Clone.Rows.Add(ra)).CopyToDataTable

Xaml :-
Main.xaml (13.6 KB)

Here your dt1 contains 2 columns only that it why it is working. take 5-6 columns in dt1
it will not work

Would you like to try this approach?

Hey Can you send the excel sheets you are working on, to make the solution more direct. Since this is a more temporary solution

Book123.xlsx (119.6 KB)

Dt1 is in sheet dt1
dt2 is in sheet dt2
and the result is in result sheet

Result is same as dt1 just 1 column is added which is extracted from dt2. using vlookup in state column. Please help to achieve this using linq.

Hey @yash.choursia,

Try the below xaml for the sheets you have sent.
AddAbrievation.xaml (7.3 KB)

Sheets: - Book123.xlsx (281.3 KB)

1 Like

Check this workflow:
Inserting Data.zip (167.5 KB)

I added “newResult” sheet on the excel file you gave just to validate the output.

This is the implemented LINQ but you need to define columns headers for dtResult first using Build Data Table activity

(From a In dt1
Group Join b In dt2
On a("State").ToString.Trim Equals b("Price Group Desc.").ToString.Trim Into grp = Group
From g In grp.DefaultIfEmpty
Select dtResult.Rows.Add(
		a("Place").ToString,
		a("State").ToString,
		If(IsNothing(g), "#N/A", g("Cust. Price Grp").ToString),
		a("Bill No.").ToString,
		a("Gross amount").ToString,
		a("Before Tax Amt").ToString,
		a("Total Bill Amt").ToString
	)
).CopyToDataTable

There is a missing ‘a’ on Gujarat on the excel file you gave, this affected the result of the workflow I implemented that caused “#N/A” value on ‘Cust. Price Group’ result
image

image

This is called a join. It’s a standard database operation. Use the Join Data Table activity.

Select ra = row1.ItemArray.Take(2).Append(If(grp Is Nothing, Nothing,grp(1))).Concat(row1.ItemArray.Skip(3)).ToArray

Whats is this line doing, can you explain?

  1. It takes first 2 items from the row and then to the end of this array it adds the Cust. Price Grp value if it matches any state otherwise it is left blank or empty

  2. Then to the above found array we add the row again by skipping the first 3 entries(i.e Place, State and Cust. Price Grp)

row1.ItemArray.Take(2) - Takes Place and State from the row
Append(If(grp Is Nothing, Nothing,grp(1))) - Adds the value of Cust. Price Grp if matched otherwise Nothing
row1.ItemArray.Skip(3) - Takes The remaining row values, i.e. Bill No.,Gross amount,Before Tax Amt and Total Bill Amt
Concat(row1.ItemArray.Skip(3)) - Will add it to the first array where we have taken Place and State and added Cust. Price Grp

Main.xaml (9.8 KB)
Try this…

Append(If(grp Is Nothing, Nothing,grp(1)))
What does this grp(1) or grp(5) indicate? which index?

  1. grp is basically each row value from the second sheet that contains state name and Cust. Price Grp.
  2. To get the value of Cust. Price Grp, we can either do grp(“Cust. Price Grp”) or grp(1) where 1 will act as the index. (0 is for first element, 1 is for 2nd element and so on)

understood. Thanks a ton.

1 Like

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