Diving list into groups before processing

[JCH]

I am trying to find the most efficient way for a process to cycle through a list of files and perform some actions.
Just to illustrate what I’m trying to do:

Every file can have any number of references. To go into a file requires a lot of steps. So I want the robot to enter all of the ref numbers that belong to that file at the same time to avoid having to go out of the file, and then back into the same one.

I can put it in a for each loop, but when will the robot know that it has to go to a new file. Something like IF FileNumber = FileNumber currently opened, then enter ref#, else exit file and open new FileNumber and enter ref#.

I’m struggling how to make that work in UiPath. Any ideas would be much appreciated!!

[karavinds (Aravind Krishnaswamy)]

Hello, can you elaborate your scenario? How to recreate your requirement?

[ClaytonM]

Hi.

I do this all the time, and I believe you will want to create an array of filenames that you want to go through, the use a For each on that array. Then, inside that array, use an Excel Read Range, and take that Data Table to enter each item using another For each.

For example, here is some pseudocode:

files = Directory.GetFiles(folderName, "*.xlsx").ToArray

For each file in files
    Excel scope, Read Range

    For each row in dt1
         => Perform interaction with application to enter each ref number using row("Ref number").ToString

So, essentially, like that.

EDIT: if you choose to use Orchestrator Queues, then each file might be your queue item.

Regards.

[JCH]

Thanks @ClaytonM and @karavinds.

I think I should have been a bit clearer. It’s just hard to explain without being able to really show the system I’m working in. So when I say “file”. I mean a file that I would process in our operating system. Not a file in a folder on my computer. Maybe record would be a better word to use.

What the robot should do is find the File in my operating system and then enter the associated Ref number as per the excel sheet. There are a lot of steps involved to get that done. Finding the record, accessing it, going to the right place to enter the Ref Number, save it, close it, go back to the search menu for instance. I can have robot iterate through the whole list and take each of those steps.

But if you’re already in a record, you can just enter all of the Ref Numbers that belong to that record. If the robot doesn’t have to go through the whole process (find the record, access it etc etc), then it would be a lot faster.

So essentially I’m trying to find a way to tell it that it can perform the entering of the reference numbers portion of the process for each Ref Number that belongs to that file/record before saving and closing that file/record. I just don’t know how to tell the robot which Ref Numbers have the same File considering that the name of the Files and Ref Numbers will always change. If I could somehow divide the excel sheet into groups based on the name of the file (in the example, A, B and C), then I can tell it to do the process for each group.

Not sure if that makes sense

[ClaytonM]

I understand kind of.

You basically want to process for each file in the excel sheet. So, you need to create a list of the distinct files (since there are multiples of each file, you don’t really want to loop through every row for every single file).

So, let’s use some lambda to take the distinct files.
Assign activity: filesUnique = dt1.AsEnumerable.Select(Function(r) r("File").ToString).ToArray.Distinct
where dt1 is the data table variable returned by an Excel Read Range, and filesUnique is an Array Of Strings.

Then, run through a For each on that array of distinct files. Inside the loop, you will create another array to only pull in the Ref Numbers that are under that file.
Assign activity: refs = dt1.AsEnumerable.Where(Function(r) r("File").ToString = file).Select(Function(r) r("Ref number").ToString).ToArray

Then, you can run through that array when you enter them into a system.

Simply put, it will look like this pseudocode:

Excel Read Range of source data

Assign activity: filesUnique = dt1.AsEnumerable.Select(Function(r) r("File").ToString).ToArray.Distinct

For each file In filesUnique
    Assign activity: refs = dt1.AsEnumerable.Where(Function(r) r("File").ToString = file).Select(Function(r) r("Ref number").ToString).ToArray

    For each ref In refs
        => enter ref into the system per file

Hopefully, that makes sense and is what you are trying to do.

If you choose not to use lambda to essentially filter down your data as you process them, then you can accomplish this with some If activities, but can clutter your logic up and processing will be slightly slower. But, thought I would share the lambda approach.

Regards.

[JCH]

Awesome, thanks a lot @ClaytonM. It will take me some time to try that out, but it definitely captures the essence of what I’m trying to achieve.

I’ll get back to you once I’ve been able to follow your instructions.

[JCH]

Thanks again @ClaytonM. It took me a little while to figure it all out with the process I’m automating. And learn about Lambda! But I got it to work. I appreciate your help in this.