Working with CSV files

Post Reply
User avatar
stevedee
Posts: 209
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 71 times
Last edited by stevedee on Thursday 30th January 2020 1:55pm, edited 2 times in total.

User avatar
grayghost4
Posts: 62
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: 209
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: 62
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

User avatar
Serban
Posts: 39
Joined: Saturday 28th March 2020 8:17am
Location: Alexandria
Contact:

Re: Working with CSV files

Post by Serban » Saturday 28th March 2020 6:40pm

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: 466
Joined: Wednesday 21st September 2016 2:22pm
Location: Guernsey, Channel Islands

Re: Working with CSV files

Post by cogier » Sunday 29th March 2020 12:48pm

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

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)

Post Reply