CSV data manipulation: Difference between revisions
Line 23: | Line 23: | ||
(define (all-rows port make-reader) |
(define (all-rows port make-reader) |
||
(define read-row (make |
(define read-row (make-reader port)) |
||
(define head (append (read-row) '("SUM"))) |
(define head (append (read-row) '("SUM"))) |
||
(define rows (for/list ([row (in-producer read-row '())]) |
(define rows (for/list ([row (in-producer read-row '())]) |
Revision as of 11:17, 23 June 2013
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>
- 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-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