Ruby で自家製スクリプトを書く 10
CSV から特定のフィールドを抜き出し、SQL 文を生成する
config.yaml
filename: csv: sample.csv sql: sample.sql encoding: output: UTF-8 input: Shift-JIS csv: fs: ', ' quote: '"' headers: false fields: - number: 2 # CSV のフィールドの番号 name: name # CSV のフィールドの名称 - number: 3 name: address - number: 4 name: zip sql: prefix: INSERT INTO `sample_table` # SQL 文の接頭辞 infix: VALUES # SQL 文の接中辞 delimiter: ; # SQL 文の区切り文字
csv2sql.rb
#!/bin/env ruby $KCODE='utf8' require 'yaml' require 'kconv' require 'rubygems' require 'fastercsv' config_file = 'config.yaml' configs = YAML.load_file(config_file) def convert_char_encoding(encoding, data) begin case encoding when 'jis' return Kconv.tojis(data) when 'Shfit-JIS' return Kconv.tosjis(data) when 'EUC-JP' return Kconv.toeuc(data) when 'UTF-8' return Kconv.toutf8(data) else exit(1) end rescue SystemExit => e p 'set invalid charcter encoding or nil.' exit(0) end end day = Time.now current_time = day.strftime("%Y%m%d_%H%M%S") fp = File.open(current_time + configs['filename']['sql'], 'w+') FasterCSV.foreach(configs['filename']['csv'], {:col_sep => configs['csv']['fs'], :quote_char => configs['csv']['quote'], :headers => configs['csv']['headers']}) do |row| p row #=> 配列の構成 values = [] column_names = [] configs['fields'].each do |filed| number = filed['number'] - 1 if (configs['encoding']['input'] == configs['encoding']['output']) values.push row[number] else values.push convert_char_encoding(configs['encoding']['output'], row[number]) end column_names.push filed['name'] end fp.puts configs['sql']['prefix'] + ' (`' + column_names.join('`, `') + '`) ' + configs['sql']['infix'] + " ('" + values.join("', '") + "')" + configs['sql']['delimiter'] + "\n" end fp.close
実行結果。
% ruby csv2sql.rb ["1", "myname", "myaddress", "123-4567"] ["2", "yourname", "youraddress", "890-1234"] ["3", "hisname", "hisaddress", ""] ["4", "hername", "heraddress", "A56-7890"] % ls 20080525_171450sample.sql csv2sql.rb read_yaml.rb config.yaml openfile.rb sample.csv % cat 20080525_171450sample.sql INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('myname', 'myaddress', '123-4567'); INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('yourname', 'youraddress', '890-1234'); INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hisname', 'hisaddress', ''); INSERT INTO `sample_table` (`name`, `address`, `zip`) VALUES ('hername', 'heraddress', 'A56-7890');