Populate array with random, unique numbers

Help
1

Hey there,

I am ultimately trying to copy rows out of Excel to another Excel list. For example, if there are 100 records in the Excel listing, I will select 5 at random. The catch: each of the 100 records needs an equal chance of being selected - so the random number generator must range from 1 - X number of rows in Excel list.

I want to create an array of random, unique numbers based on the size of the population and then use those numbers as an index when selecting the rows to copy out of Excel.

Anyone have a solution to accomplish this?

2

Can you try like this?
Enumerable.Range(0, length).OrderBy(Function(x) Guid.NewGuid).Take(count).Toarray()
Where length is an int length of your collection and count is how many you want.
Note that if count > length, it will return length elements.

3

Thanks for the reply! how would this be set up within UiPath? I usually work within an IDE like Visual Studio.

Also, how would I verify that a duplicate random value was not added to the array?

Once I get the array of random numbers, I want to use this array as an index for selecting records from Excel.

4

Above syntax was slightly off (it doesn’t want to accept underscore for an ignored value, have to use a real identifier - edited now).
You can use it in assign and put the value in int[].
For uniqueness, run it through .Distinct() call and check counts (although by Enumerable.Range as source it cannot have a duplicate, but a UnitTest doesn’t hurt - depends on how defensive you want to program it).
Example below:
Sequence2.xaml (7.7 KB)

For using it as index, once you read your excel, run a foreach on the randomizedArray and add the yourDt.Rows(i) row to a List<DataRow>.
On any IEnumerable<DataRow> you can use .CopyToDataTable() which will create your new datatable that you can write to Excel. If intellisense doesn’t show that method, just type it in full - it’s from an extension assembly that doesn’t always load correctly to intellisense, but that command will work (docs here).

Good luck!

1 Like
5

Not exactly following the steps you wanted, but this way might be a better solution to get the same outcome you want.

I created an activity that receives a datatable as an in argument, randomizes it by performing a fisher-yates/knutes shuffle, and outputs the randomized datatable as an out argument.

Knutes_Shuffle.xaml (11.7 KB)

This might be a better way to go about it as it can be used for many things. In your case, you’d simply pass in the table to be randomized, then grab ‘n’ rows, where n = number of selections required. It can be used in any other instance where randomization is needed. Re-useable code is always nice :slight_smile:

1 Like
6

I like this solution as well - which activity is used to grab the rows out of the new datatable?

7

It depends on what you want to do with it, and like most things, there are multiple ways of going about it.

If you wanted to keep in datatable format, the easiest way would be a for loop that loops from row 0 → index you specify (which for some reason UiPath doesn’t have). This can be approximated by doing a for each loop with a counter included. Then break after counter = index

Another way is to convert the randomized table to ienumerable and use the .take() method where it only takes the amount of rows you specify in the parenthesis, then add it to a new datatable.

Many other ways to go about it too, those are just the first that come to mind.

EDIT: Here is a workflow that shows how to do both of the methods I just mentioned. Note that you can just iterate over it as an enumerable object as well instead of copying to a new datatable if you aren’t changing the data within it after that point.
joshgregory10.zip (5.6 KB)

8

Awesome! Can I clone the schema of my source table which I will be pulling records from as opposed to building the schema manually within UiPath? I need this to be flexible based on the table format; we will be using different tables for this automation.

9

yes of course! I just built the table for demonstration purposes. You should pass the datatable you get directly from your excel sheet (or whatever other datatable you’re working with)

10

I am using the Write Range activity to dump the datatable (which is working great, by the way) - how can I write this data to a new Excel sheet by feeding it a file path?

11

Do you want a new sheet within the existing workbook? If so, you provide the same file path as the existing workbook, but change the sheet to be named whatever you want the sheet to be named. If the sheet you named doesn’t exist, it will create it and paste the datatable there.

If you want a completely new workbook, just provide a different file path. Make sure you use the full extension (.xlsx, .xls, .xlsm, .xlsb, etc) in the file path. Note that workbook write range will create a new workbook automatically if it doesn’t exist. If you are using excel write range, then make sure the excel application scope has the ‘CreateNewFile’ property checked

12

Thanks! automation is working except the one column (Column C) from datatable is being dropped and Shuffle Order column is being appended to the end. I am assuming this is messing it up - any suggestions? I am not sure how you wrote the indexing language.

13

Sorry for the late reply, I’ve been a bit busy.

The example workflow I gave doesn’t remove any data columns. You’d have to post your code for me to check why that’s happening. If you want to check yourself, a good way to do it is to write to CSV (with differing names) at various points throughout your code. It’ll help pinpoint where exactly the column is being removed.

ShuffleOrder column is being appended at the end by the KnutesShuffle workflow. If you don’t want it, just add a ‘Remove Data Column’ activity immediately after you run that workflow to remove it.