Array Division / Split

Good afternoon everyone,

Here is my use case:

I currently have an Oracle query that generates a list of customer ID’s. Usually anywhere between 300-500 Customer ID’s every time the query runs.

SELECT CUSTOMER_ID
FROM DB.TABLENAME

Then I have a second query that uses those generated Customer ID’s and pull additional data.

SELECT *
FROM DB.TABLENAME

WHERE CUST_ID IN (CUSTOMER_ID1, CUSTOMER_ID2, CUSTOMER_ID3…etc…)

Now I am building this process within UiPath and I generated the first query which provided me with a Datatable and a column with ALL of the Customer ID’s.
Now my problem is that I cannot feed all of those Customer ID’s at once to the second query because of technical limitations in the database (size overflow basically).

So what I am forced to do is actually split the Customer ID’s into multiple batches. For example if I have 400 Customer ID’s , I would run the query 4 times with 100 Customer ID each time.

Given that Scenario, does anyone have a good solution on how to split up the Array of Customer ID’s that I have into multiple subarrays. For example, if we had 350 Customer ID’s in the master array, we would want 100 in array 1, 100 in array 2, 100 in array 3 and 50 in array 4.

@BotAM97

following building block used within an assign activity could help:

creating a string list:
yourDataTableVar.AsEnumerable.Select(Function (row) row(YoudColumnNameOrIndex).toString).toList

Creating Segements e.g. taking second 100 elements:
YourStringList.Skip(100).Take(100).toList

Creating the comma seperated String for the In StatementPart
String.Join(“,” yourSegmentListVar)

2 Likes

@ppr - I tried your first step in “creating a string list” and I am getting the following error (I even tried the column name without quotes):

dt_CustID.AsEnumerable.Select(Function (row) row(“Extracted_CustID”).toString).toList

image

@BotAM97
ensure datasetextensions is imported have a look here

@ppr That line was already my in my .xaml file.

@BotAM97
Find a demo XAML here using a segement size of 2 items, but also can be configured by adjusting the control variables
sasho1987.xaml (9.6 KB)

1 Like

Hi @ppr , thank you for your reply, it help me develop the correct solution.

I tweaked what you told me a little bit and it worked on my end as the initial code was giving me an error.

I first assigned the datatable column into an array of strings using this code:
(From row in DATATABLE.AsEnumerable() Select Convert.Tostring(row(“COLUMNNAME”))).ToArray()

Only then was I able to use the Take() and Skip() methods in order to pull the array elements that I needed:
ARRAY.Take(400).ToArray()
ARRAY.Take(400).SKIP(100).ToArray()

Thank you so much for your help :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.