Extract values from datatable

Help Studio
, , , , , , , , , , , , , ,
1

Hi all,

In a datatable - dt1, i want to extract all the values of the that row and column, if a highlighter is added
Ex.

IMG_20220803_150129
IMG_20220803_1501291920×798 289 KB

For the above datatable, if yes is mentioned then i want to extract the values.

Ex

For the above table,

The values for the first yes is
Row- A, X.
Column- sub1

Is this possible?

Thanks in advance!

2

use filter option after extracting the data.

3

To use the filter option, the headers are not known nor are the position.
We’ll have to find if th data table has yes in it and if so, find the rows and columns of it.

4

Hi @Ray_Sha1

You can try this approach

Input Data Table

image

Template of the output data table

image

The workflow

image
image631×511 19.9 KB

dt_Result =

(
	From row In dt_Data
	Let col = row.ItemArray.Select(Function(x, i) Tuple.Create(x.Tostring.Trim.ToUpper, i)).Where(Function (t) t.Item1.Equals("YES"))
	Let colNameArr = col.Select(Function(t) dt_Data.Columns(t.Item2).ColumnName).ToArray
	Where col.Count >0
	Select dt_Result.Rows.Add({String.Join(", ", row.ItemArray.Take(2).Select(Function(x) x.ToString)), String.Join(", ",colNameArr)})
).CopyToDataTable

dt_Result is a data table which contains two columns Row and Column i.e. the output

Output

image

Please refer the xaml for more clarity

ExtractVlauesFromDT.xaml (8.9 KB)

1 Like
5

we know the headers, if that headers contain data means it fetch that row and so on…,

6

Hi,

Is there a way i can get name of the column and the first values under it.
Ex. The col value is sub1. If there’s a row under the headers and it had value sub11. Then is there a way to extract sub1 & sub11?

As shown in the picture below…

16596007099413057322964290402342
165960070994130573229642904023421920×1442 373 KB

Thanks!

7

@Ray_Sha1

Your requirement is not clear. Could you please elaborate more. Can you provide the required output.

8

The row part works fine.
For the column part, Can we extend the part of your code where it gives out the header values. And the value beneath it??

As shown in the image below:

16596029105705073866649639869132
165960291057050738666496398691323472×4624 1.88 MB

The output shld be

Rows|column
A,x. | Sub1, sub11

Can we extend that part?

9

Hi @Ray_Sha1

Try this query instead

(
	From row In dt_Data.AsEnumerable().Skip(1)
	Let col = row.ItemArray.Select(Function(x, i) Tuple.Create(x.Tostring.Trim.ToUpper, i)).Where(Function (t) t.Item1.Equals("YES"))
	Let colNameArr = col.Select(Function(t) dt_Data.Columns(t.Item2).ColumnName).ToArray
	Let colNameArr2 =  col.Select(Function(t) dt_Data.Rows(0)(t.item2).ToString).ToArray
	Where col.Count >0
	Select dt_Result.Rows.Add({String.Join(", ", row.ItemArray.Take(2).Select(Function(x) x.ToString)), String.Join(", ",colNameArr.Concat(colNameArr2))})
	).CopyToDataTable

Input

image

Output

image

XAML

ExtractVlauesFromDT.xaml (9.7 KB)

1 Like
10

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