CSV ファイルを SQL の insert into とテーブル定義に変換する

#! ruby -Ks
# coding: windows-31j

require 'rubygems'
require 'progressbar'
require 'csv'
# If you want to use 'fastercsv', please uncomment the folloing 
# 'fastercsv' を使いたいときはコメントを外す
# require 'fastercsv'

def string_type(s)
# "NULL" または "real" または "integer" または "text" を返す
  # s が nil または empty のとき NULL を返す
  if ( s.nil? )
     return "NULL"
  end
  if ( s.empty? )
     return "NULL"
  end
  sss = s.strip
  # + は一回以上、*は0回以上 ^ 行頭 $ 行末
  # 浮動小数点数  123.45, -123.45, -123.45 など
  if( ( sss =~ /[-+]?([0-9]*\.[0-9]+$)/ ) == 0 )
    return "real"
  # 浮動小数点数  123.45e-06 など
  elsif( ( sss =~ /[-+]?([0-9]*\.[0-9]+)e[-+][0-9]*$/ ) == 0 )
    return "real"
  # 浮動小数点数  123e-06 など
  elsif( ( sss =~ /[-+]?([0-9]+)e[-+][0-9]*$/ ) == 0 )
    return "real"
  # 整数
  elsif( ( sss =~ /[-+]?([0-9]+)$/ ) == 0 )
    return "integer"     
  else
    return "text"
  end
end

def read_csv_file(filename)
# CSV ファイルを読み込み，Array を要素とする Array に格納
  csv_data = Array.new

# If you want to use 'fastercsv', please uncomment the folloing 
# 'fastercsv' を使いたいときはコメントを外す
  a =  CSV.read(filename)
#  a =  FasterCSV.read(filename)
  return a

# # CSV ファイル内の\t, \r \n, \f は半角空白文字に置き換える
# # CSV.foreach で読み込むと，nil になっていることがある．あとで不便なので空文字「""」に置き換える．
#  progress_bar = ProgressBar.new("read csv file", a.size)
#  a.foreach do |row|
#    csv_row_data = Array.new
#    row.each do |e|
 #     # セルが nil のときは空文字
#      if ( e.nil? )
#        csv_row_data.push( "" )
#      else
#        csv_row_data.push( e.gsub(/\t|\r|\n|\f/,' ') )
#      end
#    end
#    csv_data.push csv_row_data
#    progress_bar.inc
#  end
#  progress_bar.finish
#  return csv_data
end 

def csv_data_field_type(csv_data, num_columns)
# a にはCSV ファイルデータの各フィールドのデータ型を "INTEGER", "REAL", "TEXT" のいずれかで設定したい
# text を1つでも含む列は "TEXT", 全て integer である列は "INTEGER", 全て integer or real である列は "REAL"

  field_types = Array.new(num_columns)
  progress_bar = ProgressBar.new("field type", csv_data.size)

  csv_data.each do |row|
    # 各フィールドの「種類」のチェック
    j = 0

    row.each do |field|
      t = string_type(field)

      if ( (field_types[j].nil?) && (t == "integer") )
        field_types[j] = "INTEGER"
      elsif( ( (field_types[j].nil?) || (field_types[j] == "integer") ) && (t == "real") )
        field_types[j] = "REAL"
      elsif(t == "text")
        field_types[j] = "TEXT"
      # t == "NULL" のときは何もしない
      end
      j += 1
    end
    progress_bar.inc

  end
  progress_bar.finish

  # ある列について，全ての行が "NULL" という場合 field_types[j] は nil のまま．"text" に書き換えて置く
  field_types.size.times do |j|
    if ( field_types[j].nil? )
      field_types[j] = "TEXT"
    end
  end

  return field_types
end

def csv_data_not_null_constraint(csv_data, num_columns)
# nil 値あるいは空文字列（つまり「""」）のデータが最低１つ入っているような列は false
  notnull = Array.new(num_columns, true)
  progress_bar = ProgressBar.new("not null", csv_data.size)

  csv_data.each do |row|
    # 各フィールドの「種類」のチェック 
    j = 0
    row.each do |field|
      t = string_type(field)
      if ( t == "NULL" )
           notnull[j] = false
      end
      j += 1
    end
   
    # num_columns の数だけデータが無いような行がありえる．その場合には，データがないセルは NULL であると解釈するので
    for i in row.size..(num_columns - 1)
        notnull[i] = false
    end
    progress_bar.inc

  end
  progress_bar.finish
 
  return notnull
end

def print_table_def(field_types, notnull, num_columns, table_name)
#  各フィールドのデータ型が配列 field_typesに入っているとして、テーブル定義を出力する．
  print "create table " + table_name + " (\n"
  j = 0
  field_types.each do |field|
    print "  a" + j.to_s + "  " + field_types[j]
    if ( notnull[j] )
      print " not null"
    end
    if ( j < (num_columns - 1) )
      # 最後の属性でないときは「,」で継続
      print ",\n"
    else
      # 最後の属性のときは「,」はない．改行のみ．
      print "\n"
    end
    j += 1
  end
  print ");\n"
end


def csv_to_sql(csv_data, field_types, num_columns, table_name)
# CSV ファイルデータを insert into 形式の SQL 文に変換する 

  progress_bar = ProgressBar.new("insert into", csv_data.size)

  num_lines = 0
  csv_data.each do |row|
    # 各行
    print "insert into " + table_name + " values ("
    j = 0
   
    row.each do |field|
      # 「,,,」のような行では、フィールド値が  nil になる。そのときは NULL
      if ( field.nil? )
        print "NULL"
      elsif ( field.empty? )
        # 元の CSV ファイルで，「空」になっているようなセル
        print "NULL"
      elsif ( field_types[j] == "TEXT" )
        # 文字列属性のときは「'」で囲む. gsub を使い、文字列中の全ての「'」は「''」に置き換える(これは SQL の流儀)
        print "'" + field.gsub("'","''") + "'"
      elsif
        # 数値（"INTEGER" または "REAL"）のときは，文字列の先頭と末尾の空白文字を取り除く
        print field.strip
      end
      if ( j < (row.size - 1) )
        # 最後のフィールドでないときは「,」で継続
        print ", "
      end
      j += 1
 
    end
   
    # num_columns の数だけデータが無いような行については  NULL をうめる
    for i in row.size..(num_columns - 1)
        print ", NULL"
    end
   
    # 最後は「);」で終わる
    print ");\n" 
    num_lines += 1
    # 10000 行ごとにコミット
    if num_lines == 10000
      print "commit;\n"
      print "begin transaction;\n"     
      num_lines = 0
    end
    progress_bar.inc 

  end
  progress_bar.finish
end

# 「file.rb」は Ruby プログラム・ソースコードのファイル名．
# 「HOGE.CSV」の部分は，CSV ファイル名を設定 （Windows の場合は「R:/hoge.csv」のような書き方をしてもよい）．
# 「T」の部分はテーブル名の設定．

# 例えば ruby file.rb R:/hoge.csv T
FILE_NAME=ARGV[0]
TABLE_NAME=ARGV[1]

csv_data = read_csv_file(FILE_NAME)
num_columns = (csv_data.map{|e| e.size}).max
field_types = csv_data_field_type(csv_data, num_columns)
notnull = csv_data_not_null_constraint(csv_data, num_columns)
print_table_def(field_types, notnull, num_columns, TABLE_NAME)

# このプログラムでは，もともとの CSV ファイルで空文字になっているようなセルは，すべて ("''"ではなく) "NULL" になることに注意
print "begin transaction;\n"
csv_to_sql(csv_data, field_types, num_columns, TABLE_NAME)
print "commit;\n"
CSV ファイルを SQL の insert into とテーブル定義に変換する

前準備

ソースコード
