Skip to content

Instantly share code, notes, and snippets.

@valvallow
Last active April 6, 2022 17:11
Show Gist options
  • Save valvallow/2005703 to your computer and use it in GitHub Desktop.
Save valvallow/2005703 to your computer and use it in GitHub Desktop.
convert csv to insert|update sql query string
#!/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)))))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment