Working with CSV files

Post your Gambas programming questions here.
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Working with CSV files

Post by stevedee »

The CSV (Comma Seperated Values) file is my favourite format when working with data; its simple to understand and its been around forever. CSV files can be opened in any text editor or imported into a spreadsheet.

Each value is seperated from the next via a comma, except the last value in a line. Multiline CSV files normally contain ordered data, where each line represents a data record and each value is the data in a field. The first line may contain field headers.

I must admit I've almost always written code from scratch to deal with CSVs. I seem to find it relaxing working with string commands like Mid, Instr & Replace.

However, I really don't need to do this every time because there is a CsvFile class hidden away in gb.Util. I've only just bothered to look at it, so I thought I'd share my notes.

In a new project I started by creating a text file and adding some CSV data. I then declared an object based on the CsvFile class and added my code to a Button_Click();
Public myCSVfile As CsvFile

Public Sub Button1_Click()
Dim aFields As String[]
Dim iRecords As Integer
Dim colRecords As New Collection

  myCSVfile = New CsvFile(Application.Path & "/" & "csvTest.csv")
  Do Until myCSVfile.Eof
    colRecords[myCSVfile.Line] = myCSVfile.Read()   'read every line into a collection
    Inc iRecords                                    'count the number of records
  Loop
  aFields = myCSVfile.Fields      'get the field headers (i.e. "name", "rank" & "serialNo")
  
  
  
As you can see, you only need a few lines of code to 'decode' the CSV file and make the data easily accessible.

Then I wrote some nonsense to display data;
  
  
  'display some data from the CSV file
Dim thisRecord As Integer
  
  thisRecord = Rnd(iRecords) + 2  '1st line=1, but this is the header row, so offset by 2
  Me.Text = "Dad's Army Roll Call: " & "Field count: " & aFields.Count & ", Record count: " & iRecords
  Label1.Text = aFields[0] & ": " & colRecords[CStr(thisRecord)]["rank"]  
  Label1.Text &= " " & colRecords[CStr(thisRecord)]["name"]
  Label1.Text &= " #" & colRecords[CStr(thisRecord)]["serialno"]
End
If your delimited file doesn't use commas, no problem!
You can specify the delimiter when you instantiate the object:-
myHashSVFile = New CsvFile ( Path As String [ , Separator As String, Escape As String ] )


I've also attached an oven-ready project, including the CSV file, to save you some time.
CSVfile_gbUtil.tar.gz
(13 KiB) Downloaded 502 times
Last edited by stevedee on Thursday 30th January 2020 1:55pm, edited 2 times in total.
User avatar
grayghost4
Posts: 174
Joined: Wednesday 5th December 2018 5:00am
Location: Marengo, Illinois usa

Re: Working with CSV files

Post by grayghost4 »

Thank you for the work and the listing ... you must have been looking over my shoulder watching my struggle with file handling, I will be looking at your code to see how I can use it :D

At the risk of insulting the teacher :o
I was informed a few days ago to use &/ instead of & "/" &

http://gambaswiki.org/wiki/lang/stringop?nh
User avatar
stevedee
Posts: 518
Joined: Monday 20th March 2017 6:06pm

Re: Working with CSV files

Post by stevedee »

grayghost4 wrote: Thursday 30th January 2020 4:10pm ... you must have been looking over my shoulder watching my struggle with file handling...
Talking of File handling, take a look at the File class in gb.Util. You can get the contents of a file like this:-
Public myFile As File
...
  myFile = New File
  TextArea1.Text = myFile.Load(Application.Path & "/csvTest.csv")
...and if you edit it, you can save it like this:-
  myFileCopy.Save(Application.Path & "/csvTest.copy", TextArea1.Text)
Really simple! Much easier than the old way.
...I was informed a few days ago to use &/ instead of & "/" &
Don't you worry, I have a very thick skin!

Neither way is right or wrong. I often prefer "wordy" to "terse" code. Long live:-
Let integerWithAVeryLongName = integerWithAVeryLongName + 1


So just go with what you are happy with.
User avatar
grayghost4
Posts: 174
Joined: Wednesday 5th December 2018 5:00am
Location: Marengo, Illinois usa

Re: Working with CSV files

Post by grayghost4 »

:evil: Thank you for you post, I have changed my program to use a csv file.
It took a while to learn to implement it, but has made loading a group of boxes on the form a snap. :D
I think I am starting to learn a little OOP . :D
it seam that rich text is not supported here, I tried to use <small> but it did not work.

I have discovered that you don't have to count the records as you read them
the Property mycsvfile.line give the last read line So
 
iRecords = mycsvfile.line
  '  then 

 For icount = 2 To iRecords   ' + 1 not needed 
    ComboBox1.Add(colRecords[CStr(icount)]["accname"])
    ComboBox1.Insert("************")
  Next


 index = ComboBox1.Index + 2   
   For Each oObj In accountinfoBoxes.Children
         oObj.Text = colRecords[Index][aFields[icount]] 
       Inc icount
   Next
User avatar
Serban
Posts: 39
Joined: Saturday 28th March 2020 8:17am
Location: Alexandria
Contact:

Re: Working with CSV files

Post by Serban »

Thanks!
I am a huge fan of text files and string manipulation.
Most of the work done by DirLister, is string manipulation in fact.
I'm thinking of how to write a book on the solutions applied in DirLister.
There is a lot of code and lots of solutions that can be Copy/Pasted. Well, almost "Copy/Paste".
Why I need the ideas outlined here?
I'm planning to move to a different level with DirLister. Build a statistics module to show me aggregated data on the time spent for the various operations, the amount of data processed and finally, the amount of time consumed. Maybe, finally, convert it into some currency to show the money spared on storage media spendings with DirLister.
Unfortunately, there still is a long way to go to get there...
Greetings! :)
The only thing necessary for the triumph of evil is for good men to do nothing.”― Edmund Burke;
It's easy to die for an idea. It is way harder to LIVE for your idea! (Me)
User avatar
cogier
Site Admin
Posts: 1118
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: Working with CSV files

Post by cogier »

I am a fan of CSV files. Over time I have discovered the power of Gambas so I can now upload a CSV file, ignoring blank lines and dealing with the inverted commas, in one line.
Dim sData As String[] = Split(File.Load("MyFile.csv"), gb.NewLine, Chr(34), True)
User avatar
Serban
Posts: 39
Joined: Saturday 28th March 2020 8:17am
Location: Alexandria
Contact:

Re: Working with CSV files

Post by Serban »

cogier wrote: Sunday 29th March 2020 12:48pm [...] in one line.
Dim sData As String[] = Split(File.Load("MyFile.csv"), gb.NewLine, Chr(34), True)
I guess this is the only efficient approach. Now, we can cycle through the items and sort them, according to the needs, as [sData] is suitable for any kind of loop, thus we can apply multiple filtering conditions, up to where we want to get.
Probably the filtering code might get a little fat, but this does the job! :D
The only thing necessary for the triumph of evil is for good men to do nothing.”― Edmund Burke;
It's easy to die for an idea. It is way harder to LIVE for your idea! (Me)
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: Working with CSV files

Post by 01McAc »

I have a question re CSV files so I need to reopen the old thread:
When I read (myCSVfile.Read() ) the CSV file the first line with headers are completely in lower case although the fist letter of the word is a uppercase letter. The DB refuses to update the data as the field names are case sensitive in sqlite3. Any ideas?

Code: Select all

For Each sFile In Dir(DirChooser1.SelectedPath, "*.exp").Sort()
          
          I = 0
          Debug sFile & "  Len: " & Stat(DirChooser1.SelectedPath &/ sFile).Size
          myCSVfile = New CsvFile(DirChooser1.SelectedPath &/ sFile)
          Do Until myCSVfile.Eof
            colRecords[myCSVfile.Line] = myCSVfile.Read()   'read every line into a collection
            Inc iRecords                                    'count the number of records
          Loop
          
          aFields = myCSVfile.Fields      'get the field headers (i.e. "name", "rank" & "serialNo"
          
          Debug "Colcounts: " & colRecords.Count
           
          For ii = 2 To colRecords.Count + 1                    ' go through every line 
            sSQL = "SELECT " & aFields[0] & " FROM Lenses WHERE " & aFields[0] & " = " & colRecords[CStr(2)][aFields[0]]
            Debug "SQL: " & sSQL  
            $Rec = DBS.$Con.Exec(sSQL)
            If $Rec.Available Then ' change/edit record
                $Query = " ID = &1"
                $Rec = DBS.$Con.Edit("Lenses", $Query, colRecords[CStr(2)][aFields[0]])
            Else 'new record
                $Rec = DBS.$Con.Create("Lenses")
            Endif
            
            For i = 0 To myCSVfile.Fields.count - 1           ' go through the whole data set within the line
              Debug "Field: " & aFields[i] & "  Content: " & colRecords[CStr(ii)][aFields[i]]     
              If (aFields[i] <> "DateCreated") Or aFields[i] <> "DateChanged" Then
                  $Rec[aFields[i]] = colRecords[CStr(ii)][aFields[i]]  
              Endif
            Next  
          Next
User avatar
PJBlack
Posts: 184
Joined: Tuesday 9th June 2020 10:26pm
Location: Florstadt, Hessen, Germany

Re: Working with CSV files

Post by PJBlack »

something like so ?
    Print Chr(Asc(Left(sString, 1)) - 32) & Right(sString, -1)
01McAc
Posts: 75
Joined: Sunday 24th January 2021 8:25pm

Re: Working with CSV files

Post by 01McAc »

Looks good but doesn't work for fields like "DateChanged" or "LensVariants". Most of my DB fields have just one uppercase letter in the beginning but some have two. My fault, I should have used just one naming convention in the DB design. Too late to change it.
Post Reply