Get the last record in a CSV file

Post your Gambas programming questions here.
Post Reply
User avatar
sarpomira
Posts: 9
Joined: Monday 28th December 2020 4:15pm

Get the last record in a CSV file

Post by sarpomira » Monday 28th December 2020 4:45pm

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

User avatar
stevedee
Posts: 377
Joined: Monday 20th March 2017 6:06pm

Re: Get the last record in a CSV file

Post by stevedee » Monday 28th December 2020 6:28pm

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

User avatar
cogier
Site Admin
Posts: 643
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: Get the last record in a CSV file

Post by cogier » Monday 28th December 2020 6:35pm

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 24 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

User avatar
stevedee
Posts: 377
Joined: Monday 20th March 2017 6:06pm

Re: Get the last record in a CSV file

Post by stevedee » Monday 28th December 2020 6:47pm

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.

User avatar
sarpomira
Posts: 9
Joined: Monday 28th December 2020 4:15pm

Re: Get the last record in a CSV file

Post by sarpomira » Monday 28th December 2020 7:28pm

Thanks for the speedy response guys.

cogier, that example you constructed works great. Wow.

cheers

User avatar
sarpomira
Posts: 9
Joined: Monday 28th December 2020 4:15pm

Re: Get the last record in a CSV file

Post by sarpomira » Monday 28th December 2020 8:44pm

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 

Post Reply