Working with CSV files
Posted: 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();
Then I wrote some nonsense to display data;
You can specify the delimiter when you instantiate the object:-
I've also attached an oven-ready project, including the CSV file, to save you some time.
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.