Process rows in excel and write output


#1

Hi,

I need expertise on the following process.
Excel file with rows of multiple columns. I need to read the file
(a) assign respective columns to variables;
(b) extract part of a value from a column say column C where value is ‘joe-bank12345-zz’ and return length of 5 characters after bank, ie output = ‘12345’;
© then write the output ‘12345’ into another column;

I need to know the formula and also the sample, if it’s easy for you to assist. Thanks !


How to remove row when cell is empty or specific cell value in excel
#2

Read Excel in ExcelApplicationScope
(a) Using Read Range to read columns and store data to DataTable.
(b) Loop the Datatable and find the Column that value is ‘joe-bank12345-zz’ and assign it to a variable “StrA”.
do StrA.substring(8,5)
© Using Write Range to write data to another column.


#3

Hello ,
You have to follow steps As @etss1016 suggested just simply add
result = Regex.Match(str,"\d+").Value
To extract numbers from given string.


#4

Thanks. I have not done any coding.

Do you have a sample or let me know the formula for that
(i) extract the value to return part of value, in this case output ‘12345’ from ‘joe-bank12345-zz’;
(ii) formula for wrtiitng of StrA output to another column in the same row. Thanks !


#5

While looping through data table use assign stage
Datatable.rows(rownumber).item(“columnc”).value = result = Regex.Match(datatable.rows(rownumber).item(“columnB”).tostring,"\d+").Value

Just increment rownumber from 0 to end of loop


#6

hello @abcdefg,

find attached file for solution.
updateexcel.xaml (7.9 KB)
Test.xlsx (8.6 KB)

if you found this working solution, please mark this as solution so that other user can easily get answers :slight_smile:


#7

Thanks so much. I just realise you are a Robot Master. I am so privileged to have met you.

I am not able to find this Regex.Match(str,"\d+").Value in the UIPath documentation for better understanding. Could I also check if the formula works if my data includes digots that I may not want.

That means, the format could be
joe-bank12345-z12’
joel-bank123456-abc238
joanna-bank1234-123d

The requirement is extract the digits following bank and before ‘-’. As such, the digits could be variable length depending on where the ‘-’ ends. Basically, I need to decode the digits from between ‘bank’ and ‘-’.

Thanks man. I am excited with RPA. It seems to be able to do wonders. Of course, with people like you in the forum to help newbies without coding background to learn!


#8

Hello @abcdefg,

updateexcel.xaml (9.9 KB)
Test.xlsx (8.6 KB)

may be this will work ? :wink:

let me know if you still facing some problems


#9

Hi @rahatadi, I just found out how to link your userid.

It works to a T. You are real fast.

Could I also be so bold to ask you to integrate the reqirement with my colleague to cut short our learning curve.

Your exmaple is good. So we wanna retain that. Now, there is another excel ‘basefile.xlsx’ with a column say ‘G’ of numeric column

I need to
(i) match Test.xsx after the output you just did to file say ‘basefile.xlsx’ column G, eg. match '‘12345’ in row 1 to a row in ‘basefile.xlsx’ that has same value;
(ii) Then when matched, append a new column in ‘basefile.xlsx’ with ‘1’, else ‘0’. That is, baseline.xlsx should have status column of found 1 and not found 0;
(iii) Then assign a variableJ to ‘12345’ with ‘@.hotmail.com’
(iv) send an email to variableJ with content

This is something we wanna explore if UIPath is easier to use compared to another software WorkFusion Express.

Thanks man !


#10

can you please upload those excel and excepted results ?
will see how that can be done…


#11

Thanks @rahatadi for being so helful.

I have attached your sample nput in additon to my baseline file for matching and also the expected result in baseline-expected-result.xlsx after process is run.

I managed to use send.outlook.mail.message to trigger email as it’s easier of the other mail options. The problem is I do not know how to assign the variable to the ‘To’ when sending. I am only exploring quck fix by fixing email at column A at the monent, eg. row(“colA”).ToString.

So if you are able to help me with matching of the two files and appending a column to indicate match status ‘1’ followed by another coloum of email address by appending column with concatenated value from G to ‘@hotmail.com’ will be good. Thanks !Test.xlsx (8.6 KB)

abc.xlsx (7.3 KB)
baseline.xlsx (8.3 KB)
baseline-expected-result.xlsx (9.8 KB)


#12

Hello @abcdefg

this may solve your problem.
Test.xlsx (8.6 KB)
baseline.xlsx (8.3 KB)
updateexcel.xaml (18.3 KB)

Regards,
Aditya


#13

Thanks @rahatadi. I run the file. However, there was no output. The baseline line was also not updated. It was ok before the change to compare two files. Thx.


#14

I have stored output in different file.


#15

Hi @rahatadi,

You have really left me in awe. I am amazed by your creative idea. I found the file. Need to confirm this.

(i) You created another output similar to baseline after appending additional columns to track status etc. Is it because it is difficult to so called update the column of a processed row ?

(ii) I searched directory for the output file listed in your program. I found it in a folder which I run yesterday. But for the latest program today, i chose to put in another folder in isolation with yesterday’s. But the output went into yesterday’s folder/ How does UIPath behave in dertermining where the output goes? The same directory from whcih it is run or that it found the same test file in yesterday’s folder (I did put everything into the folder for today’s run for completeness though).

You are the guru. Respect.


#16

(i) we can write output in same file, reason behind my action was just to compare two files are as expected or not…

(ii)it is strange scenario but any way you can change file save location. just change output file path -
(uipath can work with relative path and whole path) :slight_smile:


#17

Thanks @rahatadi. We are indeed enlightened by your thorough explaination.

If it’s not diffifult, could you just help us close this important exercise for our leaning and also all forumers to just -

(i) in the same process with the two input files but write back into the same files. That is, at the end of the process, we will see the appended columns in the respectuve two files upon matching.

I believe this will many of us as I cannot find similar exercise/activity in this forum. Thx.


#18

Can you please explain bit more?:slight_smile:


#19

Hi @rahatadi,

That means the test.xlsx input will have the columns appended in the original file instead of abc.xlsx which is separate file. Also, after matching of the two files, we would like to have baseline.xlsx with the appended columns instead of a separate output which the program produced in output_expected.xlsx.

That is, we would like the process to append both input files instead of producing the outcome in new files.

Of course, by your sharing, we have learnt so many things already. We will be grateful if you provide this use case so that we also know it’s possible to create new file and also append same files. Much thanks !


#20

Hi @rahatadi,

I traced the following error
Source : Assign
Message : Index was outside the bounds of the array.
Exception Type: System.IndexOutOfRangeException

to assignment of Dummy.Split({"-"}, StringSplitOptions.None)(1).ToString

Some rows may not have the format with ‘-’, eg. ‘hdddkhd’, ‘aioauu’, ‘yaajw’ Then the error occurs.

How do I improve on the assignment statement to handle cases where the column value do not have ‘-’. Thx