How to capture the rows count in datatable without using for each row

Help
1

Hi Guys.I have One scenario.In datatable,I want the rows count of one column corresponding to another column with out using foreachrow or foreach .Please find the screenshot here.CaptureCount
The Output Should be like:
Fruits:5
Vegetables:2
DryFruits:3

2

Hi,

You can use select statement.

Datatable.select(“columname=‘Fruits’”).copytodatatable

Use assign statement and store the above expression in datatable and now you can use this in write line newdatatatable.rows.count.tostring and do the same for others.

3

you could also just do it all in one statement:

Log: “Fruits: “+Datatable.select(”[columnname] like ‘Fruits’”).count.tostring
Log: “Vegetables: “+Datatable.select(”[columnname] like ‘Vegetables’”).count.tostring
Log: “DryFruits: “+Datatable.select(”[columnname] like ‘DryFruits’”).count.tostring

Remember that if there are no rows present for the .select method, it will throw an error, so it is always good practice to surround it with a try catch, this is why we have logged the three categories separately because you should surround each ‘log’ activity with a try catch, and in the catch section, add a similar log, but it will say “[Insert Fruit Name]: 0”. then if there is no rows with that fruit name it will log it as zero

you could also be even more advanced and create a list of unique values from column 1, and then use that to construct your output, using only values which actually exist in the data, which would avoid the problem of the .select method failing is there are not results.

1 Like
4

Actually I don’t know What will be there in column 1.So I cant give the row names here.

5

Where I have put [columnname] you can replace it with the index of the row (a number), if you don’t know the column names

6

hello @sandhyak9
do something like:

For each : (From r in dt select x = r(0).ToString).ToList.distinct

Log Message value: item.ToString & “-” & (From r in dt where r(0).ToString = item select x = r(0).ToString).Count.ToString

Next

7

Hi,

Try like this, this will give you output in Dictionary type (Dictionary(String, String)).
yourDataTable.AsEnumerable().GroupBy(Function(r) r.Field(Of String)(“Column1”)).Select(Function(r) New With {r.Key, r.Count()}).ToDictionary(Function(p) p.Key, Function(s) s.Count.ToString)