CSV spreadsheet files are suitable for storing tabular data in a relatively portable way.

The CSV format is flexible but somewhat ill-defined.

For present purposes, authors may assume that the data fields contain no commas, backslashes, or quotation marks.



Task

Read a CSV file, change some values and save the changes back to a file.

For this task we will use the following CSV file:

C1,C2,C3,C4,C5
1,5,9,13,17
2,6,10,14,18
3,7,11,15,19
4,8,12,16,20

Suggestions


 Show how to add a column, headed 'SUM', of the sums of the rows.
 If possible, illustrate the use of built-in or standard functions, methods, or libraries, that handle generic CSV files.



if set (a, open ("yourcsv.csv", "r")) < 0 then
  print "Cannot open \qyourcsv.csv\q"  ' open file a for reading
  end                                  ' abort on file opening errors
endif
 
if set (b, open ("mycsv.csv", "w")) < 0 then
  print "Cannot open \qmycsv.csv\q"    ' open file a for writing
  end                                  ' abort on file opening errors
endif
 
if read (a) = 0 then                   ' read the header line
  print "Unexpected end of file"       ' if it fails, write the error
  close a : close b : end              ' close files and terminate
endif
                                       ' process the header line
for c = 0 step 1                       ' don't know number of columns
  p = here()                           ' get input buffer position
  y = tok (ord (","))                  ' parse the first field
until p = here()                       ' until buffer position doesn't change
  write b, show (y);",";               ' write it out
next
 
write b, "Sum"                         ' add a column
 
do while read (a)                      ' read a line
  s = 0                                ' reset the sum
  for x = 0 to c-1                     ' read all columns
    y = iif (set (y, val (tok (ord (",")))) = info ("nil"), 0, y)
    s = s + y                          ' add value to sum
    write b, y;",";                    ' write the value
  next                                 ' next column
  write b, s                           ' write the sum
loop
 
close a : close b : end                ' close files and terminate
 