To select one column from different excel sheet using linq

Help Studio
1

Hey all,

Im using linq to get an output sheet named output_dt from two different sheets, sheet1 and sheet2.

I’m getting all the columns from sheet1 in my output_dt sheet which is expected, but along with that I want one column from sheet2.
How to achieve this?

Linq query is
(From D1 in DT1.asEnumerable
From D2 in DT2.AsEnumerable
Where Condition select D1).copytodatatable

In this I want to have one more column from D2.

Any ideas?

Thanks in advance.

2

@ppr can you help out?

3

Hi @Ray_Sha1

Welcome to forum

(From D1 in DT1.asEnumerable
From D2 in DT2.AsEnumerable
Where Condition select D1,D2(ColumnName)).copytodatatable

Can u try this @Ray_Sha1

4

Hi @NIVED_NAMBIAR ,
Thank you so much for replying.
Came out with error. Tried it

5

Hi @Ray_Sha1
u can try out linq join query
format

(From D1 in DT1 Join D2 in DT2 on condition
Select output.clone.LoadDataRow(New Object(){D1(ColumnName),D1(ColumnName)…,D2(ColumnName)},False)).CopyToDataTable
where output is the new datatable to which u are assigning join results

Hope it helps you

Regards

Nived N

Happy Automation

6

Hey @NIVED_NAMBIAR ,

Thank you.
But for the above query, I need all the columns from DT1.
Will it get it without mentioning all the columns from DT1?

7

Also the condition involves equals.

Errored out.

8

@Ray_Sha1

some details are unclear but keep in mind:
From D1 in DT1.asEnumerable
From D2 in DT2.AsEnumerable

is doing a cartesian product. Not sure if this was your intention.

Lets have a look on following example:
dt1:
grafik

dt2
grafik

Task:

  • left join on first columns
  • take all cols from dt1 and the comment col from dt2 for the matchers

flow:
grafik

construct the empty result datatable with the needed column structure
use a LINQ

(From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1(0).toString Equals d2(0).toString
Let ra = d1.ItemArray.Append(d2("Comment")).toArray
Select dtResult.Rows.Add(ra)).CopyToDataTable

with the help of append we can construct the new itemArray (datarow object array) easy:

  • take all columns from d1 by grabbing the d.ItemArray. Append the Comment col to the Object Array and return it as an object array, late be used for the result rows addition

By using the itemArray we get all columns from dt1 without explicit listing it.

Result:
grafik

Find starter help here:
InnerJoin_1Col_Result_AllLeft-1Right.xaml (10.5 KB)

1 Like
9

Hi @ppr,
Thank you for replying.
I don’t want to use join as I’m using the condition which checks if the values of sheet 1 is equals to the values of sheet 2 and then writes in the sheet3. I can’t use join activity for that.

Is there any other way?

Thanks again!

10

Hey @ppr ,

Comes out with an error.
Assign: Object reference not set to an instance of an object.

I reverified the columns…
Any idea on this?

Thanks in advance!

11

@Ray_Sha1

often the target datatable (in the Sample LINQ dtResult) is not initialized and from there the null exception comes from)

this was the initial question:

with itemArray construction and appending dt2 cols to dt2.ItemArray it is answered

whatever condition you want to use is up to you. Working with a Cartesian Product is quite similar to an inner join.

However, in case of you need some further help so share with us some sample data for the input and a clear description on the excpected output.

12

Hi @ppr,

Thank you so much for the clarification.

In the given query, can I add two columns and write it in the third column?
All this in the same sheet?
Is it possible?

13

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