Working with CSV files

Post Reply
User avatar
stevedee
Posts: 198
Joined: Monday 20th March 2017 6:06pm

Working with CSV files

Post by stevedee » Thursday 30th January 2020 1:41pm

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 10 times
Last edited by stevedee on Thursday 30th January 2020 1:55pm, edited 2 times in total.

User avatar
grayghost4
Posts: 56
Joined: Wednesday 05th December 2018 5:00am
Location: Concord, CA usa

Re: Working with CSV files

Post by grayghost4 » Thursday 30th January 2020 4:10pm

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: 198
Joined: Monday 20th March 2017 6:06pm

Re: Working with CSV files

Post by stevedee » Thursday 30th January 2020 6:25pm

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: 56
Joined: Wednesday 05th December 2018 5:00am
Location: Concord, CA usa

Re: Working with CSV files

Post by grayghost4 » Saturday 01st February 2020 4:06pm

: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

Post Reply