Writing specific data to Excel

[Jasu]

Hi,

I want to subtract values from two columns in an Excel file, and write the results to three different output Excel files depending on the results of the subtraction: negative, positive or zero.
I have created a ‘Read Range’ activity to read the original Excel file, ‘For each row’ to iterate through the rows and then an ‘If’ control where in condition I wrote something like: cint(row(1)) - cint(row(2)) < 0 then ‘Write Range’ activity to write to a new Excel file with the original Excel file as an input (to get the file with only those with row(2) is smaller than row(1)) . I get the original file generated with no change. Any idea? Thanks.

[Palaniyappan (Forum Leader)]

Buddy @Jasu
Welcome back to UiPath community buddy

Well for this case if you use write range you will get the same table without any change…rather we can create three datatables by using build datatable activity…like out_dt1, out_dt2, out_dt3
All these three tables must have all the columns as string and same no. Of columns as in first excel output datatable and the order of columns must be similar as well …
– then one you have done validation with a if condition, you can use add data row to add that current row from for each row loop to the respective table based on the condition you have madr in the if condition like positive, negative or equal to zero…
– in add data row pass the input as arrayrow like {row(0),row(1),row(2),row(3)}
I have given four values considering like there are four columns in all the table…

Similarly you can use add datarow in each part of if condition based on the validation like either THEN or ELSE part of if condition…

Thats all buddy you are done…
Kindly try this and let know whether this works or not buddy
Cheers @Jasu

[ronanpeter]

Here is an example of your logic in a workflow.

From this input

It analyses Values1 - Values2

If the result is less than zero, it outputs the data for that row to another datatable and ignores the others.

This is the result

Main.xaml (7.6 KB)

[Jasu]

Great tips @Palaniyappan, I’ll try this out. Thanks and much appreciated man.

[Jasu]

Thanks @ronanpeter for your help and for the useful sample, will try out. Cheers man.

[Jasu]

A question related to this: I don’t need to write anything to Excel if a condition is not fulfilled. For example, now if the condition cint(row(1)) - cint(row(2)) < 0 is not met, a new Excel file is generated but with no data. How to NOT write/generate any Excel output if a condition is not met?

[ronanpeter]

Put the write CSV activity in an IF statement.

Then make the activity conditional on there being rows in your output datatable

If dt.rows.count > 0

[Palaniyappan (Forum Leader)]

Buddy remove the activity that enters data to the excel like write range or write cell in ELSE part of the if condiion @Jasu so that it wont enter any data to the excel if the condition gets failed
Cheers @Jasu

[Jasu]

Thanks @Palaniyappan. Still getting an empty Excel file as an output.

[ronanpeter]

Change your condition to dt1.rows.count = 0

[Palaniyappan (Forum Leader)]

Buddy change the condition accordingly buddy
like if you want the count to be equal to be zero and if so change it accordingly buddy
Cheers @Jasu

[Palaniyappan (Forum Leader)]

did that work buddy @Jasu

[Jasu]

@Palaniyappan Not yet my friend, still trying to figure out what is the issue:

Any suggestions are much welcome. Thanks.

[Palaniyappan (Forum Leader)]

buddy where you are getting the value for DT1 buddy
you did have read the excel with read range activity buddy
so the row count in DT1 will be zero only buddy
thats why going to THEN part of if condition buddy
Kindly use read range activity to read the file and get the output variable from that activity as DT1 and pass here buddy
or
if you have datatable ready, with you even before read range activity into a excel, check whether it has any rows in it buddy
Cheers @Jasu

[Jasu]

Thanks @Palaniyappan, worked finally .

[Jasu]

Thanks @ronanpeter.