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.



----------------------------------------
-- Simplified CSV parser (without escape character support etc.).
-- First line is interrepted as header with column names.
-- @param {string} csvStr
-- @param {string} [sep=","] - single char as string
-- @param {string} [eol=RETURN]
-- @return {propList}
----------------------------------------
on parseSimpleCSVString (csvStr, sep, eol)
  if voidP(sep) then sep=","
  if voidP(eol) then eol = RETURN
  lines = explode(eol, csvStr)
  if lines.getLast()="" then lines.deleteAt(lines.count)
  res = [:]
  res[#header] = explode(sep, lines[1])
  res[#data] = []
  cnt = lines.count
  repeat with i = 2 to cnt
    res[#data].append(explodeBySingleChar(sep, lines[i]))
  end repeat
  return res
end
 
----------------------------------------
-- Simplified CSV creater (without escape character support etc.).
-- @param {propList} csvData
-- @param {string} [sep=","]
-- @param {string} [eol=RETURN]
-- @return {string}
----------------------------------------
on createSimpleCSVString (csvData, sep, eol)
  if voidP(sep) then sep=","
  if voidP(eol) then eol = RETURN
  res = ""
  put implode(sep, csvData[#header])&eol after res
  cnt = csvData[#data].count
  repeat with i = 1 to cnt
    put implode(sep, csvData[#data][i])&eol after res
  end repeat
  return res
end
 
----------------------------------------
-- Explodes string into list
-- @param {string} delim
-- @param {string} str
-- @return {list}
----------------------------------------
on explode (delim, str)
  if delim.length=1 then return explodeBySingleChar(delim, str)
  l = []
  if voidP(str) then return l
  dl = delim.length
  repeat while true
    pos = offset(delim, str)
    if pos=0 then exit repeat
    l.add(str.char[1..pos-1])
    delete char 1 to pos+dl-1 of str
  end repeat
  if pos=0 then pos = 1-dl
  l.add(str.char[pos+dl..str.length])
  return l
end
 
----------------------------------------
-- Explode string into list based on single char delimiter
-- (uses Lingo's build-in 'item' support, therefor faster)
-- @param {string} delim
-- @param {string} str
-- @return {list}
----------------------------------------
on explodeBySingleChar (delim, str)
  l = []
  if voidP(str) then return l
  od = _player.itemDelimiter
  _player.itemDelimiter = delim
  cnt = str.item.count
  repeat with i = 1 to cnt
    l.add(str.item[i])
  end repeat
  _player.itemDelimiter = od
  return l
end
 
----------------------------------------
-- Implodes list into string
-- @param {string} delim
-- @param {list} l
-- @return {string}
----------------------------------------
on implode (delim, l)
  str = ""
  cnt = l.count
  repeat with i = 1 to cnt
    put l[i]&delim after str
  end repeat
  delete char (str.length-delim.length+1) to str.length of str
  return str
end