Process rows in excel and write output

I don’t have 2018.3 (I’m using 2018.1) so there are errors trying to open your workflow.

I was going to create a workflow to answer your question, but the rules are a little unclear. You say to “find the matching row in dt1” - but you don’t specify how to match dt1 rows to dt2? How do I determine what is a match?

Also, do you want a separate output file, or do you just want a new column in dt2/baseline.xlsx named “dt2 found indicator” and have it be 1 for found and blank if false?

When you changed the formula to cint instead of convert.toint32 did that not solve the issue? One other thing I noticed is that you are using generic type ‘object’ variables in your data columns you’re adding. I would recommend changing them to a specific datatype unless you are expecting to have more than one datatype within a single column

Hi @Dave,

I have included the screen capture.

The logic in the workflow is as such. It is able to match the row but the challenge is after matching, I need to write a status indicator in output file of matched row if the condition in file1 is met.

Here it goes.
(i) Read file1 (test.xlsx) – d1
(ii) Determine from columnB (eg. value joe-banfdk12345-zz), a delimted value after ‘-‘, append new column ‘12345’ into output file. Call this column_1 and this shall be the key in file1.
(iii) Read file2 (baseline.xlsx); key field is columnH, eg. ‘12345’
(iv) Loop thru file2 – d2; for every row in d2 find matching row in d1, then append column with status = ‘1’ (found)
(v) It is working till this stage
(vi) Challenge now is I need another column to indicate in file2 output, ie d2, the matched row in d1 satisfies file1 dt1.columnD > 0. This is where the error message appears.

Yes, I have use cint too. The error seems to suggest cannot bind, means cannot find the d1 contents using the proposed condition cint(row(rownumber).item(3)) or Convert.ToInt32(row(rownumber).item(3))? So I am not sure if in d2 loop, it is not able to read d1 contents by the expression which we are trying. Not sure as I am just beginner.

The added data column should have similar data type. They are character field to store the delimited search field in file1; and also status indicators in file2.

I have also re-attached the files see if you could read the program. The word doc for screen capture should be able to provide the program content.

Much thanks.
baseline.xlsx (8.4 KB)

screenshots.pdf (1.0 MB)
Test.xlsx (9.8 KB)
updateexcel-test.xaml (25.2 KB)

updateexcel-test.xaml (28.4 KB)

check this
@abcdefg

Thanks @rahatadi

I will try to modify my codes accordingly. Just to clarify though. What is done now is to
(i) Read the first input file (test.xlsx) again before writing output_expected.xlsx after matching of the key which was done in previous program.
(ii) That means now we do a 3rd reading in the process before we write the output_expected.xlsx.
(iii) Then during this 3rd reading, determine if the condition is met where columnD in first file is >0; and write the status indicator into output_expected.xlsx.

I want to understand how UIPath works here. At the 3rd reading of the file, how does the process associate dt2.rowX is matching a row in dt1.rowY where dt1.rowY coumnD > 0 ?


Thanks.

Because output of highlighted activity is stored in dt2.

I was bit confused with what to compare so i did this way…it can be shorten

Hi @rahatadi

Thanks for clarifying. Understand it’s not easy to describe in words. My apologies.

I will do like you advised previously. I have attached a sample output_expect.xlsx. A picture paints a thousand words.

You will find the codes working after matching of the key field. My challenge now is, in the same output_expected.xlsx file, I need to have additional status to indicate if condition in dt1 of colD (test.xlsx column D) > 0. The matching key remains the same.

Hope you are able to advise if the latest code which you provided, I reattach here is for the requirement I clarify herebaseline.xlsx (9.6 KB)
. Thanks !
output_expected.xlsx (10.2 KB)
Test.xlsx (10.0 KB)
updateexcel-test.xaml (28.4 KB)

1 Like

Hi @rahatadi

I have tried as suggested. While it is able to read the values in dt3 where colD > 0, it not able to associate to the key, that is match to the corresponding row in dt2.

Please advise based on the output sample I provided. Thanks !

baseline.xlsx (9.6 KB)

output_expected.xlsx (10.2 KB)
Test.xlsx (10.0 KB)
updateexcel-test.xaml (28.4 KB)

Hi @rahatadi,

Are you able to advise on the problem which I described earlier. Thanks !

Hi,

Is there anyone who could advise. Thanks.

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