Share via

Small Basic: Read and Write CSV Files

  • Article

What is CSV?

Comma separated values (CSV) is a file format that is used to exchange data between programs. It uses commas to separate values and requires equally many values on each line.

Example

For a game, we want to save the best player’s score, its name and the date on which the score was achieved. The format we choose therefore is: [player’s name], [score], [date]

Bob,12,2013-01-02

This means that Bob scored 12 points at 2013-01-02. We can easily store multiple scores by writing them one below another.

Alice,15,2013-03-04
Bob,12,2013-01-02
Charlie,9,2013-05-06

Observation:  CSV files can hold one to many records or lines with values.

Example

We have a sensor that saves the measured temperature in a file. The first value of a line describes the hour, the second the minute, and the third the temperature. The format is: [hour], [minute], [temperature]

7,38,12
7,39,12
7,40,13
7,41,13
7,42,13

For instance, the first line means that at 7:38 a temperature of 12 were measured.

Observation: It can be difficult to understand a CSV file if you don’t know what every value stands for – what the format is.

Implementation

Read CSV

To load a CSV file, a while-loop reads line for line. Within the loop the subroutine ExtractValues() is called to extract all values from the line. After the loop is finished, all values are written to TextWindow by ShowValues().

filename = "data.csv"
line_number = 1
 
'-----------------------------------------------------------------------------
'   reads line for line, saves values to csv and shows them
'-----------------------------------------------------------------------------
line = File.ReadLine(filename, line_number)
 
While line <> ""
  ExtractValues()
   
  line_number = line_number + 1
  line = File.ReadLine(filename, line_number)
EndWhile
 
ShowValues()
 
 
 
'-----------------------------------------------------------------------------
'   ShowValues - writes all values of csv to the TextWindow
'-----------------------------------------------------------------------------
Sub ShowValues
  For line_number = 1 to Array.GetItemCount(csv)     
    For value_number = 1 to Array.GetItemCount(csv[line_number])
      TextWindow.Write(csv[line_number][value_number] + " ")
    EndFor
    TextWindow.WriteLine("")
  EndFor
EndSub

The ExtractValues() subroutine works on the current line. It extracts all values and saves them into the CSV array at the according position.

'-----------------------------------------------------------------------------
'   ExtractValues - saves all values of the current line to csv
'-----------------------------------------------------------------------------
Sub ExtractValues  
  'repeat as long as there is a comma
  While Text.GetIndexOf(line,",") > 0
    comma_position = Text.GetIndexOf(line, ",")     
     
    'save text until first comma
    values[Array.GetItemCount(values)+1] = Text.GetSubText(line, 1, comma_position-1) 
     
    'remove saved text and comma from the line
    line = Text.GetSubTextToEnd(line, comma_position+1)
  EndWhile
   
  'last value does not have a comma at the end
  values[Array.GetItemCount(values)+1] = line
   
  'append read values to csv
  csv[Array.GetItemCount(csv)+1] = values   
   
  'reset variable
  values = ""
EndSub

Finally, all values of the CSV file can be accessed by the CSV array. For instance, you can get the third value of the second line by: csv[2][3]

Write CSV

To save an array as a CSV file, it has to be processed row for row. All values of one row are combined to one text whereby commas are inserted between the values. This text is then written to the CSV file.

'see http://social.technet.microsoft.com/wiki/contents/articles/15062.small-basic-array-basics.aspx
csv = "1=1\=1\;2\=2\;3\=3\;;2=1\=4\;2\=5\;3\=6\;;3=1\=7\;2\=8\;3\=9\;;"
csv_filename = "data.csv"
 
For line_number = 1 to Array.GetItemCount(csv)
  line = csv[line_number][1]
   
  For field_nr = 2 to Array.GetItemCount(csv[line_number])
    line = line +  "," + csv[line_number][field_nr]
  EndFor
   
  ' write line to file
  File.AppendContents(csv_filename, line)
EndFor

References

Other Languages