convert csv to insert|update sql query string

csv2sql.scm

#!/usr/local/bin/gosh

(use srfi-1)
(use text.csv)
(use file.util)
(use util.list)
(use gauche.parseopt)

(define (usage)
  (print "Usage: csv2sql [options ...] <csv-file>")
  (print "  h|help - print this usage")
  (print "  k|keys - primary key column names (delimiter=,)")
  (print "  d|delimitter - (default=,)")
  (print "  n|tablename")
  (print "  t|tsv")
  (print)
  (print "SQL query")
  (print "  default => insert")
  (print "  -k|keys => update")
  (print "Table name and Column name")
  (print "  Table name: <csv-file> name without extentions")
  (print "  Column name: header row column names")
  (exit 1))

(define (csv->list file :optional (delim #\,))
  (port->list (make-csv-reader delim)
              (if file
                  (open-input-file file)
                  (current-input-port))))

(define (csv->sql csv table-name . key-col-names)
  (if (null? csv)
      '()
      (let ((header (car csv))
            (csv (cdr csv)))
        (if (null? key-col-names)
            (csv->insert-str csv table-name header)
            (apply csv->update-str csv table-name header key-col-names)))))

(define (path->filename-without-ext path)
  (receive (dir filename ext)
      (decompose-path path)
    filename))

(define (keys-str->list keys-str)
  (string-split keys-str #\,))

(define (csv->insert-str csv table-name column-names)
  (define (make-values row)
    (intersperse "," (map (pa$ format "'~a'") row)))
  (map (^[row]
         (format "INSERT INTO ~a ~a VALUES ~a;"
                 table-name
                 (intersperse "," column-names)
                 (make-values row)))
       csv))

(define (csv->update-str csv table-name column-names . key-col-names)
  (define (make-set row)
    (apply string-append
           (intersperse
            ", "
            (fold-right (^[colname val acc]
                          (if (member colname key-col-names)
                              acc
                              (cons (format "~a='~a'" colname val) acc)))
                        '() column-names row))))
  (define (make-where row)
    (apply string-append
           (intersperse
            " AND "
            (map (^[key-col-name]
                   (let1 idx (list-index (pa$ equal? key-col-name)
                                         column-names)
                     (format "~a='~a'" key-col-name (~ row idx))))
                 key-col-names))))
  (map (^[row]
         (format "UPDATE ~a SET ~a WHERE ~a;"
                 table-name
                 (make-set row)
                 (make-where row)))
       csv))

(define (main args)
  (let-args (cdr args)
      ((help "h|help" => usage)
       (keys "k|keys=s")
       (delim "d|delimitter=s" #\,)
       (tsv? "t|tsv")
       (tablename "n|tablename=s")
       (else (opt . _)
             (print "Unknown option : " opt)
             (usage))
       . rest)
    (let ((csv (csv->list (and (not (null? rest))(car rest))
                          (if tsv? #\tab delim)))
          (filename (cond ((and (null? rest) tablename) tablename)
                          ((null? rest) "<TableName>")
                          (else (path->filename-without-ext (car rest)))))
          (keys (if keys (keys-str->list keys) '())))
      (for-each print (apply csv->sql csv filename keys)))))