How to find the highest value in a CSV file

csv

#1

Hi, I am trying to go through a CSV file that I got from Yahoo Finance for GOOG > historical data > download ( I did all this through a web recording using clicks and type into and then saved the CSV file) and find the highest value in a column and save the date that this value is for. I use a sequence and read csv activity, but am stuck from there. Should I use an assign activity and then a for each activity?


#2

You could use DataTable Select operation:

dateString = csvData.Select("Column1 = MAX(Column1)").First.Field(Of String)("Column2")

Where the values are in Column1 and the dates in Column2, assumed to be strings. Theoretically of course, there could be more occurrences of the maximum value, of which this is taking the first. If you’d like to evaluate those, cut off the functions after Select, which itself returns a DataRow[], the array of table rows matching the Select condition(s).


#3

Thank you @sfranzen !! This works, the only issue is that is only outputs the date, not the number and the date. I will work for a solution to this!


#4

Then you will need to do it in multiple steps. First do the select part: maxRow = csvData.Select("Column1 = MAX(Column1)").First, where maxRow is a DataRow type variable. Then get both the date and value from this row with the maxRow.Field(Of String)(...) method as described, or with the quick and dirty maxRow(...).ToString that will also work in this case. The value will still be a string though, so you will have to convert it if you need a numerical variable.


#5

Well @sharks14

You can try @sfranzen way as well but i have also one more way to do this :slight_smile:

No need to use for each for this :slight_smile:

You can get the highest column value for “Close” Column like this

Int maxval = Convert.ToInt32(data.Compute("max([Close])",String.Empty))

one more way is Datatable.select() as @sfranzen mentioned.

there one more is to use IEnumerable.max method as well :wink:

String maxval = datatable.AsEnumerable().Max(Function(r) r("Close")).ToString

Feel free to use any one of them.

and if you wants to fetch the date as well from the max close amount row.

try this :
(Datatable.Select("[Close]=’"+ienumerable_Datarow.Max(Function® r(“Close”)).ToString+"’"))(0)(0).ToString

This is the first thing came into mind. @andrzej.kniola you can add more things i guess haha :slight_smile: :wink:

sample_shark.zip (3.0 KB)

Regards…!!
Aksh


Excel Automation task