CSVをSQL文に変換する

• vallog: csvをsqlにする（insert, update）

以前、必要だったのでザックリ書いたスクリプトだけど、便利だったんで結構使用頻度が高い。使うたびに修正したり機能追加されてる。あんまりちゃんとしたスクリプトでもないけど重宝してる。こういうのって普通はExcelとかで生成したりするもんなんだろうか？昔いた会社ではExcelで生成してたなあ。あれはあれで慣れれば便利なのかもしれん。慣れたくないけどな。

• 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)))))

view raw csv2sql.scm hosted with ❤ by GitHub

SQLつながりだけど、プログラマのためのSQL第4版が出てるんですね。ちょっと読んでみたい。第2版だったかなー、本屋で数時間立ち読ｍ（ｹﾞﾌﾝｹﾞﾌﾝ