Page 1 of 2

Working with CSV files

Posted: Thursday 30th January 2020 1:41pm
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

Re: Working with CSV files

Posted: Thursday 30th January 2020 4:10pm
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

Re: Working with CSV files

Posted: Thursday 30th January 2020 6:25pm
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.

Re: Working with CSV files

Posted: Saturday 1st February 2020 4:06pm
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

Re: Working with CSV files

Posted: Saturday 28th March 2020 6:40pm
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! :)

Re: Working with CSV files

Posted: Sunday 29th March 2020 12:48pm
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)

Re: Working with CSV files

Posted: Sunday 29th March 2020 2:11pm
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

Re: Working with CSV files

Posted: Thursday 25th February 2021 5:16pm
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

Re: Working with CSV files

Posted: Thursday 25th February 2021 5:25pm
by PJBlack
something like so ?
    Print Chr(Asc(Left(sString, 1)) - 32) & Right(sString, -1)

Re: Working with CSV files

Posted: Thursday 25th February 2021 6:22pm
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.