How do i perform a sequence of activities on an Excel sheet?

Hi guys!

I’m relatively new to UiPath and although i’ve had success in creating a few scripts, but they are largely similar in terms of function i.e. read excel, copy selected data from it, paste them into another excel file and make use of these data to run some processes.

However, i’m trying something new and that is to work on the actual excel file itself but i have no idea how do i go about doing it. Am i able to only work on the file by reading the excel file as a datatable then output it again?. Example: Imgur: The magic of the Internet

I’m tidying up the data within this excel as a lot of them are old and contain irrelevant information. So here are the steps that i’d like to perform:

  1. Only extract out the 1st 3 tokens in the name, or any name tokens before the first special characterlike brackets, @, S/O etc

Example 1: ABCD EFGH IJKL S/O MNOP QRST @ UVWX YZ, i’d only need ABCD EFGH IJKL
Example 2: ABCD D/O MNOP QRST @ UVWX YZ, i’d only need ABCD

The data that’s cleaned up should be placed in column ‘E’.

  1. Next, i’ll need to sieve out rows that has a positive NET_LIABILITY_AMT (by using the number filters function in Excel, we’ll normally use “Greater than” selection)

  2. Lastly, to filter away Phone numbers that contains no value (Excel function: Filter)

My apologies for not being able to upload the excel file. I’m a new user so the forums doesn’t allow me to upload files.

Once again, thanks to all in advance. Please feel free to let me know if there’s any other information that I can provide.

Thank you, appreciate it.

1 Like

Hi @Psyence,

Yes, using excel as datatable would be the best way to achieve this.

You can use regular expressions here to filter data as per your requirements and then add that data to column E(create column E in the datatable and add data to it)

you can use ‘Filter data table’ activity to easily perform these steps.

Use a delete range activity and then write range to insert filtered out data into the excel sheet.

3 Likes

Hi Vinutha, thank you so much for your reply! I really appreciate it!

Though i’ve yet to try it out yet, but i think i have a general idea of how this is going to work out by using Filter Data Table. I do have some questions though:

We would most likely be able to filter out the special characters (brackets, @, %, tabs etc.) by using filter table. But how do we go about limiting the number of name tokens? and in my example before, how would we be able to decide the number of name tokens depending on where the special characters are?

Pardon me if i’m missing something obvious :frowning:

@Psyence,
Below is the regex to match everything until the special character. The special characters are in the brackets [ ]. Add/remove according to your requirements. You can add this filtered result into last column.
.+?(?=[@|$|#] )