CSV data manipulation

CSV spreadsheet files are portable ways of storing tabular data. The CSV format is either flexible or ill defined, depending on a point of view, allowing for delimiters besides comma.

The task here is to read a CSV file, change some values and save the changes back to the same 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

Racket

lang racket

(require (planet neil/csv:1:=7) net/url)

(define make-rosetta-csv-reader

 (make-csv-reader-maker
  '((separator-chars              #\,)
    (strip-leading-whitespace?  . #t)
    (strip-trailing-whitespace? . #t))))

(define (all-rows port make-reader)

 (define read-row (make-rosetta-csv-reader port))
 (define head (append (read-row) '("SUM")))
 (define rows (for/list ([row (in-producer read-row '())])
                (define xs (map string->number row))
                (append row (list (~a (apply + xs))))))
 (define (->string row) (string-join row "," #:after-last "\n"))
 (string-append* (map ->string (cons head rows))))

</lang> Example: <lang racket> (define 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")

(display (all-rows (open-input-string csv-file) make-rosetta-csv-reader)) </lang> Output: <lang racket> C1,C2,C3,C4,C5,SUM 1,5,9,13,17,45 2,6,10,14,18,50 3,7,11,15,19,55 4,8,12,16,20,60 </lang>

REXX

<lang rexx>/* REXX ***************************************************************

extend in.csv to add a column containing the sum of the lines' elems
21.06.2013 Walter Pachl
- - - - /

csv='in.csv' Do i=1 By 1 While lines(csv)>0

 l=linein(csv)
 If i=1 Then
   l.i=l',SUM'
 Else Do
   ol=l
   sum=0
   Do While l<>
     Parse Var l e ',' l
     sum=sum+e
     End
   l.i=ol','sum
   End
 End

Call lineout csv 'erase' csv Do i=1 To i-1

 Call lineout csv,l.i
 End

</lang> Output

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