Extract values from excel- datatable

Help Activities
, , , , , , , , , , , , , ,
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_20220812_123424
IMG_20220812_1234241920×689 168 KB

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

Ex

For the above table,

The values for the first row which has yes and okay are:
Row- A, X.
Column “okay”- Mcol3, subcol3
Column “yes”- Mcol5, subcol5

The final output stored in a datatable.
Is this possible?

Thanks in advance!

2

Hi @Ray_Sha1

Please refer the below links to understand filtering data table in different ways.

Hope this will be helpful. Thank you.

1 Like
3

can you show how the datatable should look like

1 Like
4

Hey,

The output can look like…

Row| column okay. | column yes
A, X |Mcol3, subcol3.| Mcol5, subcol5

This is using just the first row of the input datatable that I’ve used in the question above.

5

Hi,

The requirements is different.

Thanks though!

6

Hi @Ray_Sha1

Try this approach

Input

image

Output Template

image

LINQ Used

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

Output

image

Xaml for reference

ExtractVlauesFromDT_2.xaml (10.4 KB)

1 Like
7

Hello @Ray_Sha1

You can use read range to read the excel and get the output to a datatbale. Then use filter datatable activty. There you can provide multiple filtering conditions to extract the required data from it.

Thanks

8

Hey,

The solution work’s fine.

thanks!!

9

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