I have a datatable with column headings.
I need to filter the datatable based on column heading that contains specific string and retrieve the values available in that particular column.
you can start to explore the filter datatable activity and on the column tab it can be configured to keep only a particular column - result: datatable
we can use YourDataTableVar.DefaultView.ToTable(false, “{YourColName}”) - result: datatable with only the configured column
As it is unclear which resulting datatype is needed it could also the case that you are interested on a list/array of the values
arrValues = YourDataTableVar.AsEnumerable(Function (x) x(YourColNameOrIndex).toString).toArray
we can modify the result on datatype (list, array) and returned item datatype (string, int…) as needed
My column heading index varies dynamically. For example, (refer screenshot)
aa12 in column index 0 will be as aa34 in column index 3 tomorrow.
So I need to search each column heading that contains “aa” and get values in that column
-
First find out the column index of the required column as below.
-
Initialise index with 0.
For Each item in dtInput.Columns If(item.Contains("required value") Then take index value Else increment index value by 1. -
And then follow the steps mentioned by @ppr and pass index value to it.
the index we can retrieve by following:
colIDX = yourDataTableVar.Columns.Cast(Of DataColumn).toList.FindIndex(Function (x) x.ColumnName.StartsWith(“aa”))
the column name or Null if not present we can get by:
colName = yourDataTableVar.Columns.Cast(Of DataColumn).Where(Function (x) x.ColumnName.StartsWith(“aa”)).FirstOrDefault()
with the understanding that in a datable we can get many aaXX cols and we want to get all aaXX col names we can do
arrColNames =
(From c in yourDataTableVar.Columns.Cast(Of DataColumn)
Where c.ColumnName.StartsWith("aa")
Select c.ColumnName).toArray
That’s not the case.
If column heading name contains “aa”, I need to retrieve all the values available in that particular column.
we were talking about the part where we want to calculate the detail column name. Value retrieval was already discussed above
sound like the case, that more then one aaXX columns like aa12, aa34 can be present in the datatable. And on this case was the post aligned. Otherswise refine requirements in a clear, sharp format including all expected variations as well.
There won’t be multiple column name starting with “aa”.
In my datatable, the character followed by “aa” can change.
As per above screenshot, please help in retrieving values 1,4,7 by filtering column heading that contains “aa”.
every building part was already given above. You can implement it
this is why clear req descriptions are needed. Above statement can talk about 1 or multiple columns starting with aa.
However you can implement. Let us know the result. Thanks
Read your datatable, which I assume will be in xlsx? If so, uncheck Add Column Headings. Your first column will always, then, be Column1, etc. If you will always be filtering on the same column? then in your filter datatable activity instead of the column header name put “Column1”, etc
Hope that helps
Chris
Let me re-iterate, if i get your statement right (w.r.t data table).
Today, you datatable will look like below
Tomorrow, it will look like below.
If this understanding is correct, let me know which column you want to extract (Index- 0 or 3).
This actually worked. But if no columns found with Column heading that contains “aa”, then the default value “0” is assigned.
So the column index 0 values are displayed in output.
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
Anil_G
sharazkm32
ashokkarale
Darshan_Sable
V_Roboto_V
lrtetala
mively
sonaliaggarwal47
adi.mehare
iamjaredm