Extract values from excel- datatable

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.

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!

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

can you show how the datatable should look like

1 Like

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.

Hi,

The requirements is different.

Thanks though!

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

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

Hey,

The solution work’s fine.

thanks!!

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