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’;
(c) 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 !
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”.
(c) Using Write Range to write data to another column.
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 !
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
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!
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.
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)
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).
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 !