How to get the specific number of rows from the datatable?

Learn
1

HI All,

I have a datatable , from that datatable I have to extract only specific rows which eventually becomes small data table as it is a set of multiple datarows .
How do I achieve this?

Please help

1 Like
2

Hi.

You have a few options for this.

A simple way is to use the Filter Data Table activity, where you can set certain conditions. However, this can get restricted and also make data processing more difficult sometimes since it splits up the data set.

Another way, which I use frequently is to use .net code in an assign activity to form an array of rows that match my conditions but keeps the entire data set in tact.
An example of that is:

matchedRows = dt1.AsEnumerable.Where(Function(r) r("column1").ToString.Trim.PadLeft(8,"0"c) = invoiceNum.PadLeft(8,"0"c) ).ToArray

IF matchedRows.Count > 0
   [do something with rows]
Else
   //there were no rows found

As you can see, in that solution, you would want to check its count before doing anything with it to make sure your row(s) are found with your conditions.

Let me know if I can help further.

Regards.

2 Likes
3

FYI, that is using Studio. StudioX might need a different approach.

4

Hey this looks good , but where to apply condition here?
I want to apply like if a row contains value "Result " and another row contains value “Total” so in that datatable I need to take rows from row containing “Result” upto the row containing “Total”.
SoIt will become a small data table

5

Sure you can do this, but can you give an example of your data so I can see how you are trying to extract it?

If the data is simply like this:

column1         column2       column3
      a            b             1
      c            d             2
      e            f             3
                Total            6

Then, we can use .ItemArray next to the row to collect all items in the row as an array and check if it contains “Total”. Also, to make it not case-sensitive, can use .ToUpper.

Here is what the .Where() would look like:
matchedRows = dt1.AsEnumerable.Where(Function(r) Not r.ItemArray.Select(Function(x) x.ToUpper).Contains("TOTAL") ).ToArray

That would basically just pull in all data that doesn’t have the Total row.

If your data is something different and this would need adjustments to work, then provide an example of the data you are working with, and I can help further.

Regards

1 Like
6

Note: You can also use the Filter Data Table activity and add a condition that <> (does not equal) Total
as I mentioned previously.

7

Hey , Thanks for the help your solution is really close .
lets consider this example

ab1 ab2 ab3 ab4 ab5
ac1 ac2 ac3 ac4 ac5
ad1 ad2 ad3 ad4 ad5
ae1 ae2 ae3 ae4 ae5
af1 af2 af3 af4 af5

here I have to take row from from containing row value ac1 to the row ae1. means I need to extract data from row 2 to row 3.
and ultimatly I have 3 rows in the output datatable

8

I see. So, in this case, you would need to find the index of the row that contains ac1 and the index that contains ae1. Then, by using those two numbers, we can use .Skip() and .Take() on the data as an array of rows, to pull in the data between those rows.

Here is an example:

matchedRows = dt1.AsEnumerable.Where(Function(r) r(0).ToString.ToLower.Contains("ac1") ).ToArray

IF matchedRows.Count > 0
    firstRowIndex = dt1.Rows.IndexOf(matchedRows(0))
Else
    Throw BusinessRuleException since row was not found

matchedRows = dt1.AsEnumerable.Where(Function(r) r(0).ToString.ToLower.Contains("ae1") ).ToArray

IF matchedRows.Count > 0
    lastRowIndex = dt1.Rows.IndexOf(matchedRows(0))
Else
    Throw BusinessRuleException since row was not found

extractedData = dt1.AsEnumerable.Skip(firstRowIndex).Take(lastRowIndex - firstRowIndex + 1).ToArray

extractedData can be a data table or an array of rows. An Array of rows will keep the entire data together, for example if you want to update those select rows, then write the entire data back to the spreadsheet.

To use it as a new data table, change .ToArray to .CopyToDataTable, like:
extractedData = dt1.AsEnumerable.Skip(firstRowIndex).Take(lastRowIndex - firstRowIndex + 1).CopyToDataTable

You can also adjust the condition inside the Where to use the column name instead of the index, or to go further, you can use .ItemArray if you don’t know which column the value would be in.

Here is an example of that:
matchedRows = dt1.AsEnumerable.Where(Function(r) r.ItemArray.Contains("ae1") ).ToArray

Additionally, if you don’t want to include the rows which you matched, simply adjust the index found in the following assignment:
extractedData = dt1.AsEnumerable.Skip(firstRowIndex).Take(lastRowIndex - firstRowIndex + 1).ToArray
Like if you don’t want to include those rows, add 1 to first index and subtract 1 from last index:
extractedData = dt1.AsEnumerable.Skip(firstRowIndex+1).Take(lastRowIndex - firstRowIndex - 1).ToArray

Hope I explained that well. (most of it is done in an Assign activity.)

Regards.

3 Likes
9

Hey ClaytonM,

Thanks for the phenomenal solution , In my case I have to include the matched rows so for that

my expression will be this
extractedData = dt1.AsEnumerable.Skip(firstRowIndex-1).Take(lastRowIndex - firstRowIndex +1).ToArray ?

and also yes I dont know in which column my values will be hence

this will be the better option for me
matchedRows = dt1.AsEnumerable.Where(Function(r) r.ItemArray.Contains(“ae1”) ).ToArray

10

to include, it will be the first expression:
extractedData = dt1.AsEnumerable.Skip(firstRowIndex).Take(lastRowIndex - firstRowIndex +1).ToArray

If the first row is found at index 3 (row 4), it will skip 3 rows, and start on row 4. If the last row is found at index 10 (row 11), it will take 10-3+1 = 8 rows. That means it will take rows 4,5,6,7,8,9,10,11 (8 rows, including row 4 and 11)

Regards. :smiley:

2 Likes
12

Hey ClaytonM,

Thankyou so much for the amazing solution ,. it worked honestly.

Its really phenomenal the way you explained .

Much Thanks!

1 Like
13

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