Excel write to corresponding column of every cell

Hey everyone,

Quick question.

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---------------

Thanks for help inadvance

1 Like

Hey @berkaykor,
I don’t see a nice way of doing it without a for each row…
Perhaps someone else has a better solution.

But What I would do is:

  1. Read range - To get the full excel in a data table.
  2. Do for each row on the datatable and add the values
  3. Use Write range on the excel.

This way you don’t have to read and write every excel row, which would be a lot slower.

Cheers!

Hello @berkaykor,

If it a large excel you can use linq for it

1 Like

Het @berkaykor,

I made a linq query for your process.

(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

Hopefully this solves your problem.

1 Like

Sorry for the delay, Thank you for your query.

I will try and let you know about the result

Hey @Akshay07,

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?

Hi,

Object Reference not set to instance of an object is faced when no or blank value is assigned, so check if there is any data retrieved.

Hey @ziga.hanzic

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

tried ur code, getting error AsEnumerable is not a member of System.data.datatable

Hello @Saranyajk,

that is a common issue faced by the UiPath user

check this post for the solution

1 Like

Thanks, the error has gone now, cheers

Hi,

I have tried the query with assigning dtresult=dtdata.clone()

But actually its working without an error but its writing on the wrong column and all other columns cells are empty.

Edit:: Save Mail column is actualy the 8th column on the datatable but its writing on 3rd.

@Akshay07 @Saranyajk

@berkaykor

Select dtResult.Rows.Add(New Object(){d(“Number”),d(Sent Mail),u})).CopyToDataTable

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?

Hello @berkaykor,

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.

Thank you for your efforts.

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