I have an excel doc(xlsx) where there is number column. The values on the column are not unique, so there several of same numbers on this column. What i want to do is, other than using for each row,
I want to write “yes” to the column “Sent Mail” where the number equals “prodNum” variable.
ex:
Number---------Sent Mail
12----------------
12---------------
13---------------
14---------------
let say prodNum=12
after execution
Number---------Sent Mail
12----------------yes
12---------------yes
13---------------
14---------------
(From d In dtData.AsEnumerable
Let u = If(d(“Number”).toString.Trim.Equals(“prodNum”),“yes”,Nothing)
Select dtResult.Rows.Add(New Object(){d(“Number”),d(Sent Mail),u})).CopyToDataTable
I have tested your query and I am getting “Object reff not set to an instance of object”, I believe I am getting this error because actually I have more than these 2 “number” & “sent mail” columns, is it the reason?
What I did:
-read range for reading the excel and save it to dtData
-assign: dtResult = your query
-write dtResult to a excel.
I get error in assign activity. Could elaborate on that?
I faced the same error while using other linq query and I solved it by initializing the Result datatable by using bulid datatable you can either use this or use dt= datatable.clone() or dt = New System.Data.DataTable
here add all the column name you require in datatable like
(From d In dtData.AsEnumerable
Let u = If(d(“Number”).toString.Trim.Equals(“prodNum”),“yes”,Nothing)
Select dtResult.Rows.Add(New Object(){d(“Number”),d(“Sent Mail”),d(“columnname3”),d(“Columnname4”),u})).CopyToDataTable
Please mark this reply as solution if it solves your problem.
With a few adjustments I made it work thank you very much, BUT it deletes the data on the column where we write “yes”, so for some numbers that column is already writtten as yes, so I dont want these data to be deleted. Is there any workaround for that?
I assume you want to keep the value as same if the prodnum doesnt match with row in Number Column value than for it instead of nothing just place the value there
eg.
(From d In dtData.AsEnumerable
Let u = If(d(“Number”).toString.Trim.Equals(“prodNum”),“yes”,d(“Sent Mail”))
Select dtResult.Rows.Add(New Object(){d(“Number”),d(“Sent Mail”), d(“columnname3”),d(“Columnname4”) ,u})).CopyToDataTable
Please mark this reply as solution if it solves your problem.