LINQ to read CSV and sort by Column; output to specific format

[PPIM_RPA]

species-listings-by-current-range-county-report_macomb.xlsx (11.2 KB)

Hello Experts,

Can LINQ be used for following task?
Read the csv, deduplicate by common name, then return results for only Status of Endangered or Threatened.

The output should look like this:
According to the report, there are # mammals (list), # Birds (list), # reptiles (list), # clams (list), and # flowering plants (list) listed as threatened and/or endangered for [County].

Thank you in advance for your suggestions.

[Anil_G (Anil Gorthi)]

@PPIM_RPA

You can do something like this

string.Join(",",Dt.AsEnumerable.Where(function(x) x("ColumnName").ToString.Equals("EnDangered")).Select(function(x) x("ColumnNmae1").ToString).Distinct)

Replace the column names as per your actual column names

Cheers

[supermanPunch (Arpan)]

Hi @PPIM_RPA ,

Maybe we can also check for dynamic way :

1. We can prepare the Output Template String/Text as required :

OutputText = "According to the report, there are -ListOfSpecies- listed as threatened and/or endangered for [County]."

Here, -ListOfSpecies- is the placeholder and we can populate it after performing the computation on the count of Species with names.

2. Read CSV / Reading of Excel sheet have to be done either by using Activities available or custom code when not possible through activities. After the Excel/CSV sheet is read as a Datatable, lets say DT, we can perform the conditions on it and prepare the data also by grouping :

SpeciesList = String.Join(", ",DT.AsEnumerable.Where(Function(x){"Endangered","Threatened"}.Contains(x("ESA Listing Status").ToString)).GroupBy(Function(x)x("Group").ToString).Select(Function(x)x.Count.ToString+" "+x.First.Item("Group").ToString+" ( "+String.Join(",",x.Select(Function(y)y("Common Name").ToString).ToArray)+" )"))

Here, SpeciesList is a variable of type String.

3. Next, we can replace the Placeholder with the computed value :

OutputText = OutputText.Replace("-ListOfSpecies-",SpeciesList)

Implementation :

Debug Visuals :

[postwick (Paul Ostwick)]

Why not use these simple activities…

• Read CSV
• Remove Duplicate Rows
• Filter Data Table

[PPIM_RPA]

I just verified this works.
So elegant. So beautiful. So appreciative.