I have one excel file in which there are 10 Columns and I need to check with a condition if it was true I need to update the values in the D column using linq query? Can anyone help..?

Hello,

I have one excel file in which there are 40 Columns and based on certain conditions I need to update the values in the C, D columns
Can anyone help me to write Linq for each to update the values in multiple columns?

@ppr @Yoichi @NIVED_NAMBIAR

Hi @PRASHANT_GABHANE

What is the condition? Need more information to construct the LINQ.

I have another excel where the ID is stored.
I need to search the ID in the my input excel if the ID is found I need to update the column C, D as “Found”

Hi @PRASHANT_GABHANE ,

Hope below video will help you to achieve this task.

Thanks.

2 Likes
  1. make ID String Array
  2. loop datatable row
  3. check that ID String Array contains row’s ID Column value

refer below code

Dim dt_Code As DataTable = New DataTable(“table1”)
dt_Code.Columns.Add(“id”, GetType(String))
dt_Code.Columns.Add(“value”, GetType(String))
dt_Code.Rows.Add({“1”,“a”})
dt_Code.Rows.Add({“2”,“b”})
dt_Code.Rows.Add({“3”,“c”})
dt_Code.Rows.Add({“4”,“d”})
dt_Code.Rows.Add({“5”,“e”})

'ID String Array
Dim arr_ids() As String = dt_Code.AsEnumerable().Select(Function(r) r(“id”).ToString).ToArray

Dim dt As DataTable = New DataTable(“table2”)
dt.Columns.Add(“id”, GetType(String))
dt.Columns.Add(“check”, GetType(String))
dt.Rows.Add({“1”,""})
dt.Rows.Add({“3”,""})
dt.Rows.Add({“5”,""})
dt.Rows.Add({“7”,""})
dt.Rows.Add({“9”,""})

For Each r In dt.Rows
r(“check”) = If(arr_ids.Contains(r(“id”)),“Found”,“Not Found”)
Next r

linq_check_result

1 Like

Hello @park363

Thank you for your reply,

I just want to achieve this using Linq.
With just one assign statement.

Thank you,
Prashant

Hi @PRASHANT_GABHANE

Try this

(From r In inputDT
Let s = If(otherDT.AsEnumerable.Select(Function (x) x(0).ToString).ToArray.Contains(r(0).ToString), "Found", "Not Found" )
Let a = r.ItemArray.Take(2).ToArray
Let b = a.Append(s).toArray
Let c = b.append(s).ToArray
Let d = c.Concat(r.ItemArray.Skip(4).ToArray).ToArray
Select outDT.Rows.Add(d)).CopyToDataTable
  • Assuming that the ID Column is the first column in both the excels.
  • C and D columns will have status values as Found and Not Found.
  • From the first 4 columns 1st column is of ID, 3rd and 4th of status, 2nd column and other 36 columns will remain as it is.
1 Like

Yes, one way is to iterate through each column and perform some kind of set operation. For example, in the LINQ query below we are using a merge() operator to append a new item to an existing list or array for every column with the same key. We use yield return for each iteration so that you can tell which data belongs where.
The code can be reviewed here:

public static IEnumerable<List> LinqForMultiColumnsWithUpdate(IDataSet data) {
var result = ; //empty list of rows //iterate through each row in the DATASET-items object and store it into another var variable that is lists public static.

Linq can be used to update a multiple columns using an excel if statement.

var query = “”; var data = new DataSet(); foreach(var row in sheet) {
} query=" UPDATE Inventory SET qty=DATEADD(“l”, 30, DATEDIFF(“d”, yesterday, row[“date”]))- qty WHERE date<=row[“date”];" ; data.Provider=new MemoryDataAdapter((IEnumerable )cast list as IEnumerable ); SqlCommandManager sqlcmdmgr1 = new SqlCommandManager (); sqlcmdmgr1.connectionstring=“server=(local);database=‘Tables’;Integrated Security=True;” __________

1 Like

Yes, one way is to iterate through each column and perform some kind of set operation. For example, in the LINQ query below we are using, Manganato What is the condition? Need more information to construct the LINQ

I have one excel file in which there are 10 Columns and I need to check with a condition if it was true I need to update the values in the D column using linq query?
Can anyone help…?

Hello @Sai_Pappu ,

As a best Forum practice, when you have a question, create a new post on the forum.

Best,
Charbel