Page 1 of 1

Get the last record in a CSV file

Posted: Monday 28th December 2020 4:45pm
by sarpomira
Hello,
Boy am I happy I found Gambas3 and this forum :-)
After programming VB6 and VB.net for many years, moved to Python for many of the control and MQTT "internet of things" projects that I have.
I also run Linux, which limited me from good GUI applications. Until now.
Gambas is awesome, light, fast, and perfect for building GUI applications.
I run into some snags with the slight nuance differences in the BASIC syntax but that's not a big issue.
The current project is the building of a GUI dashboard to display various parameters in a greenhouse.
Light, Temperature, pumps on/off.

Currently am struggling with a CSV file and was hoping to find a few members with some experience with what I'm trying to do.
One of the greenhouse sensors publishes data to a .csv file.
The text format is: date, time, temperature (with no header headings).
Like this....
27/12/2020,18:23:33,13.62
27/12/2020,18:23:49,13.62
27/12/2020,18:24:04,13.62
27/12/2020,18:24:20,13.62

I was planning to use the CsvFile library from the internal gb.util
but I can't find any documentation on how to use it.
I have included the cb.lib in my project and coded the Opening of my file like this....

===============================
Public myCSVfile As CsvFile

' Load file
myCSVfile = New CsvFile(Application.Path & "/" & "Greehouse_Sensor_Data.txt")
Do Until myCSVfile.Eof
< SOME CODE HERE>
Loop

The goal of this project is to grab the last line (specifically the last temperature reading) in the csv file.
The file gets updated every 15 seconds, but I just need to grab the last temperature reading every 10 minutes or so in order
to check it against alert/alarm settings.

Simply put:
How do I grab the last data item in a CSV file regardless of the number of records in the file?

It is not important to me whether or not the the CsvFile / gb.util method is used as long as I can read the last data point in the file.

thanks kindly

Re: Get the last record in a CSV file

Posted: Monday 28th December 2020 6:28pm
by stevedee
sarpomira wrote: Monday 28th December 2020 4:45pm ...How do I grab the last data item in a CSV file regardless of the number of records in the file?

It is not important to me whether or not the the CsvFile / gb.util method is used as long as I can read the last data point in the file...
Take a look at this post, but do come back if you still have a problem: https://forum.gambas.one/viewtopic.php?f=4&t=819

Re: Get the last record in a CSV file

Posted: Monday 28th December 2020 6:35pm
by cogier
Hi sarpomeira and welcome to the forum.

Have a look at the attached program and see if that helps.

Image
GetLastCSV-0.0.1.tar.gz
(11.68 KiB) Downloaded 249 times
EDIT

Here are a few comments for the code.
Public Sub Form_Open()

  Timer1.Trigger  'The timer is set for 10 mins so this just gets it run once

End

Public Sub Timer1_Timer()

  Dim sFile As String[] = Split(File.Load(Application.Path &/ "Greehouse_Sensor_Data.txt"), gb.NewLine, "", True) 'This splits the file by line and ignores any blank lines

  With GridView1
    .Clear
    .Columns.Count = 3
    .Rows.Count = 1
    .Columns[0].Title = "Date"
    .Columns[1].Title = "Time"
    .Columns[2].Title = "Temp"
    .[0, 0].Text = Split(sFile[sFile.Max])[0] 'Split the last line in the file and display the 1st item (.Max is the same as .Count -1. "," is the default split separator)
    .[0, 1].Text = Split(sFile[sFile.Max])[1]
    .[0, 2].Text = Split(sFile[sFile.Max])[2]
    .Columns.Width = -1                       'Auto adjusts the Column widths
  End With

  Label1.Text = "Last checked at " & Str(Time(Now))

End

Re: Get the last record in a CSV file

Posted: Monday 28th December 2020 6:47pm
by stevedee
sarpomira wrote: Monday 28th December 2020 4:45pm ...but I just need to grab the last temperature reading every 10 minutes or so in order
to check it against alert/alarm settings...
It also occurs to me that if you only read the last (most recent) record, it might be easier to build the csv file the other way up, i.e. add the most recent record to the top of the file each time.

Re: Get the last record in a CSV file

Posted: Monday 28th December 2020 7:28pm
by sarpomira
Thanks for the speedy response guys.

cogier, that example you constructed works great. Wow.

cheers

Re: Get the last record in a CSV file

Posted: Monday 28th December 2020 8:44pm
by sarpomira
Stevedee,

With a few mods, your code works also.
Here's how I did it.

Thanks

 'Requires the addition of the gb.util library
Public myCSVfile As CsvFile

Public Sub cmdRead_Click()
  
  Dim filepath As String
  Dim aFields As String[]
  Dim iRecords As Integer
  Dim colRecords As New Collection
  Dim thisRecord As Integer

  '************** LOAD FILE *************
  myCSVfile = New CsvFile(Application.Path & "/" & "Greenhouse_Sensor_Data.txt")
  Do Until myCSVfile.Eof
    'read every line into a collection
    colRecords[myCSVfile.Line] = myCSVfile.Read()   
    'count the number of records using the Inc-rement method (adds +1 to the iRecords variable)
    Inc iRecords
  Loop
  
  'Get the Field cound of the csv file
  aFields = myCSVfile.Fields
  
  'Get the Number of lines (Records)
  iRecords = myCSVfile.line
    
  '********** DISPLAY DATA ***********
   Me.Text = "Greenhouse Sensor Data: " & "Field count: " & aFields.Count & ", Record count: " & iRecords

  'Prints the number of records
  txtRecordCount.Text = iRecords
  
  'Print a full record (In this case the last one)
  ThisRecord = iRecords
  
  txtFullRecord.Text = colRecords[CStr(thisRecord)]["Date"] & "," & colRecords[CStr(thisRecord)]["Time"] & "," & colRecords[CStr(thisRecord)]["Temperature"]
  
  txtLastElement.text = colRecords[thisRecord]["Temperature"]
  
End