LINQ query to tranform DT information to an array based on column information

[Russell_Robot]

Hello Experts,

I have DT with Species and Status as below.
I would like to export using LINQ to array based on status.
One array for Threatened Species, One for endangered, and one for proposed.
Thank you for your help.

Species	Status
Canada Lynx	Threatened
Gray Wolf	Endangered
Indiana Bat	Endangered
Northern Long-eared Bat	Endangered
Tricolored Bat	Proposed Endangered
Piping Plover	Endangered
Red Knot	Threatened
Copperbelly Water Snake	Threatened
Eastern Massasauga (=rattlesnake)	Threatened
Clubshell	Endangered
Northern Riffleshell	Endangered
Rayed Bean	Endangered
Round Hickorynut	Threatened
Snuffbox Mussel	Endangered
Hine’s Emerald Dragonfly	Endangered
Hungerford’s Crawling Water Beetle	Endangered
Karner Blue Butterfly	Endangered
Mitchell’s Satyr Butterfly	Endangered
Poweshiek Skipperling	Endangered
Dwarf Lake Iris	Threatened
Eastern Prairie Fringed Orchid	Threatened
Houghton’s Goldenrod	Threatened
Lakeside Daisy	Threatened
Michigan Monkey-flower	Endangered
Pitcher’s Thistle	Threatened
American Hart’s-tongue Fern	Threatened

[Shiva_Nikhil]

Hi @Russell_Robot

Based on column information

MENTION Your Column Name

dt.AsEnumerable.Select(Function(r) r("ColumnNmae).ToString).ToArray

Hope this helps

[postwick (Paul Ostwick)]

This would be a complicated LINQ query, if it’s even possible. Easier to understand doing it with activities.

Load your data into DT_Main

Create a dictionary Dict_Tables(of string,System.Data.Datatable) (make sure to initialize it in the Default value of the variables pane)

For Each Row in Datatable: DT_Main.DefaultView.ToTable(true,“Status”)

• Assign Dict_Tables(CurrentRow(“Status”).ToString) = New System.Data.DataTable (this intializes the datatable inside the dictionary)
• Filter Data Table from DT_Main into to Dict_Tables(CurrentRow(“Status”).ToString) with the filter being “Status” = CurrentRow(“Status”).ToString

After this runs, you’ll have a dictionary of datatables based on Status, and the code is completely dynamic - you could add another status to the original data and it’ll just pick it up and create a datatable for it.

[Russell_Robot]

Getting BC30332 when trying this

[Shiva_Nikhil]

can you try this once more

DatatableVariable.AsEnumerable.Select(Function(r) r(“ColumnName”).ToString.trim).ToArray

columnname is which column You want

Example You can see below

dt is a datatable Hvaing Two Columns
[Name,Value
155,12
200,30
]
dt.AsEnumerable.Select(Function(r) r(“Name”).ToString.Trim).ToArray

Output
string[2] { “155”, “200” }

[Russell_Robot]

That outputs all items in “Status” column. I’m looking to filter by threatened, endangered. Seems to need another select. Still learning LINQ

[lrtetala (Lakshman Reddy)]

Hi @Russell_Robot

Try this

threatenedArray = (From row In dtInput.AsEnumerable()
Where row.Field(Of String)(“Status”) = “Threatened”
Select row.Field(Of String)(“Species”)).ToArray()

endangeredArray= (From row In dtInput.AsEnumerable()
Where row.Field(Of String)(“Status”) = “Endangered”
Select row.Field(Of String)(“Species”)).ToArray()

proposedArray= (From row In dtInput.AsEnumerable()
Where row.Field(Of String)(“Status”) = “Proposed”
Select row.Field(Of String)(“Species”)).ToArray()

[Shiva_Nikhil]

Hi @Russell_Robot

Sorry For the Trouble

hope this helps you
Example

[Name,Value
155,Status
200,Status
,Remarks
,Failed
]

dt.AsEnumerable.where(Function(r) r(“Value”).Tostring.Equals(“Status”)).Select(Function(r) r(“Value”).ToString).ToArray

Output
string[2] { “Status”, “Status” }

For your Input :

dt.AsEnumerable.where(Function(r) r(“Status”).Tostring.Equals(“Threathend”)).Select(Function(r) r(“Status”).ToString).ToArray

[Shiva_Nikhil]

@Russell_Robot

sample as your Excel data, I have excuted

Hope You like it

[Russell_Robot]

That gave the output I was looking for. Thank you.

[Russell_Robot]

I appreciate your help. Sorry for my bad English. Thank you for your time

[Shiva_Nikhil]

Thanks happy automation