Process rows in excel and write output

If i understand your requirement correctly
You want to spilt string with another string?

Hi @rahatadi,

I troubleshoot for the error and found that some rows could have columns without ‘-’. Most have though. But even for one row that have non ‘-’ value, the program executed with the error and I spend quite some time trying to figure out. Then found out is data problem. How could I enhance to handle such anomaly in data values. Thx.

Regards.

Hi @rahatadi,

I have attached my sample program with slight change to read the column B instead of test.xlsx. The input files are also attached. I tried troubleshooting but could not because I cannot figure out the mapping of the columns.

The last row is a record in column B of excel that does not have ‘-‘. That’s when it failed with the error message.

I now also have another issue. After I remove the last row that does not have the ‘-‘ to get it working. But the matching does not work anymore. I observe though that the delimited value in column B is not appended to the last column of the excel in output abc.xlsx but instead overwritten.

Hope this is will provide info for you to assist me in correcting the program so that I can understand the logic. Thx.

baseline.xlsx (8.4 KB)
Test.xlsx (9.8 KB)
updateexcel-test.xaml (18.7 KB)

Hello @abcdefg

This is an exceptional case, so treat this as a business rule exception :relaxed:
Just add try catch block to assign stages where this assignment was done.

Regards,
Aditya

Thanks @rahatadi.

Could you also look at my sample program and hekp me check where it went wrong as the matching does not work after i placed in more columns into test.xlsx. I am still trying to figure out pending your advise and really unable to find out as UIPath properties do not have much code and one has to undertand how it works. And i believe I am not able to grasp the assignemtn of columns. Please help so that I could take a look after your corrected the program and I could earn and compare the two versions. Much thanks and appreciation for your generous help.

updateexcel-test.xaml (25.2 KB)

may be this will help :slight_smile:

Thanks @rahatadi. You are good and thorough. I understood where the problem is now. It’s in the assigment of the column where i tired to use item(1) instead of item(13) to append Coulmn_1 to. I will follow your example to use column labels.

I will also test out the try catch. Thank you bro, you have sowed a lot of goodwill and you will reap multifold.

Hi @rahatadi,

So far making headway. Thanks.

If I want to retain the same program and customise, how could I read the test.xlsx for say coumn D and identify values that are <> 0 and match it back to baseline using the same delimited value as key to both files.

That is, for key = 1234 in both files as matching value, I should be able to set indicator in baseline (row with key 1234) if column D has value <> 0.

Thx again.

if you want to read say from column D, you can do it like this…image

and for other you can refer the previous code.

this are some advance topic, just try it :slight_smile:

:slight_smile:Get Non-matched Items.xaml (9.7 KB)
Get Unique Values.xaml (5.4 KB)

Hi @rahatadi,

Thanks for responding so fast. So advanced formula. But I am not looking at matching of column D.

Just to clarify though. The original requirements in your program remain the same.

That is, delimit column B of test.xlsx >> joe-banfdk12345-zz >> 12345.
Then use column H in baseline.xlsx, say 12345 to match with test.xlsx. They work fine. Much thanks for your help.

The new requirement now is, in the same input files, I would want to have another column in baseline.xlsx (output file) to indicate which row in test.xlsx have column D where numeric values not equal to 0 . As such, I am not looking for matching of column D. Instead, it is to determine the value of column D greater than 0 and put a column indicator in baseline.xlsx
baseline.xlsx (8.4 KB)

Hope you could provide help again. Thx
Test.xlsx (9.8 KB)

Hi @abcdefg

Just use same logic with if “convert.toint32(row(rownumber).item(colummName).tostring) >0”

Regards,
Aditya

Thanks again @rahatadi. I will try it out.

Hi @rahatadi,

I got this error while looping in d2 using the condition suggested to compare value of column D in d1. Thx.

@abcdefg

Use assign stage before if condition
And try to convert your current row into integer using same statement
And then compare it.

Hi @rahatadi,

Sorry, I am lost. Ps provide example. Thx.

First try removing the .ToString portion in the if statement, it isn’t necessary.

Second. If that doesnt work then remove Convert.ToInt32, and change it to cint - I’d almost always recommend using cint instead of convert.ToInt32 anyways. Convert.ToInt32 is a function, whereas cint is an operator: vb.net - Integer.Parse vs. CInt - Stack Overflow

If that doesnt work then he is recommending to do the following:

Create a variable of type int32 and name it TempInt

Put the below assign activity right before your if statement:

Assign Tempint = Convert.ToInt32(row(rownumber).item(3))

Then change your if statement so it is as follows:

TempInt > 0

1 Like

Thanks @Dave. I will try it and figure as much as I could.

Hi @rahatadim

I got the same error as ealier one. This is after I assign into a temp variable called length.

That is length = Convert.ToInt32(row(rownumber).item(3)) before the IF stmt.

Do you think the error is trying to suggest it cannot find item(3) which is column D of test.xlsx which is the first file output to dt1.

The loop in in dt2 where I try to read if dt1 (test.xlsx) column D satisfies condition of column D (item3) > 0

Thanks !

Change it to cint(row(rownumber).item(3))

Also what are the variable types for row, rownumber, and length?

Hi @Dave,

The following are the variable datatype
Rownum Int32
length Int32
dt1 and dt2 are datatable

I have also attached the program and the two input files. Test.xlsx (dt1). baseline.xlsx (dt2).

In the program, after reading of dt2, find the matching row in dt1 and determine if ColumnD of dt1, ie. (item3)> 0 and indicate a status in output excel of dt2 found indicator, say ‘1’. Thanks.baseline.xlsx (8.4 KB)

Test.xlsx (9.8 KB)
updateexcel-test.xaml (25.2 KB)