Filtering DataTable for first 5 numbers

Help Activities
, , , ,
1

Hi folks,
I have an Excel sheet which I have imported as a datatable. I want to filter that datatable to just show records for the first 5 unique references in a particular column - e.g.Company #1, Company #2, Company #3. There may be multiple rows per Company, and I want everything for the first 5 companies. The numbers may change every time the Excel is run through the bot, and are not sequential.
Is there a straightforward way of doing this that I’m not seeing?
Thanks!

2

@R_O_S
getting the first 5 Company names you can do following:
arrNames = YourDataTableVar.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim).Distinct().Take(5).ToArray

Filtering the datatable with this 5 Company Names we can do e.g. with LINQ
Assign activity
dtFiltered =

(From d in YourDataTableVar.AsEnumerable
Where arrNames.Contains(d(ColNameOrIndex).toString.Trim)
Select d).CopyToDataTable

This is one of many options we can do the case

3

Safe to say I would have been a long time figuring that out myself! Thanks Peter, will give this a try

4

Hey Peter,
Just trying this - tried to test the first portion by logging the arrNames output and am getting a blank String array.

I’ve done an assign activity with arrNames=dtBookings.AsEnumerable.Select(Function (x) x(0).ToString.Trim).Distinct().Take(5).ToArray

Output is System.String

Apologies if I’ve missed something obvious here!

5

statement looks fione on the first view.

  • set a breakpoint on the activity using this statement
  • debug and get paused
  • enter the statement into the immediate panel / wathc panel and check the result
6

Thanks Peter. I’m running 2018.4.4 so I don’t have the full range of Debug functions. I’ve tried stepping through it in Execute and checked the logs, but not seeing any errors, just getting a blank String array output