CSV ファイルを SQL の insert into とテーブル定義に変換する
前準備
- Ruby のインストール が済んでいること
* 「Ruby プログラミング」の Web ページを参考にしてください.
- プログレスバーを使いたい.
* gem を使ってインストールする場合
gem install progressbar
* (参考)FreeBSD では ports を使ってインストールできす
cd /usr/ports/misc/ruby-progressbar make make install
- fastercsvを使いたい.
* gem を使ってインストールする場合
gem install fastercsv
* (参考)FreeBSD では ports を使ってインストールできす
cd /usr/ports/misc/ruby-fastercsv make make install
ソースコード
【特徴】
CSV ファイルを読み込んで、リレーショナルデータベースに格納する機能をもったソフトウェアは数多くあります. わざわざ新しいプログラムを作ったのですが、その「特徴」は次の通りです.
- 巨大な CSV ファイルが扱える (1ギガバイトの CSV ファイルを読み込んだことがあります) ※ fastercsv に感謝
- CSV ファイル中の「'」は, SQL 文に変換するとき「''」に変換している
- 各列のデータ型は CSV ファイルのデータの中身から推定します.テーブル定義の SQL が自動生成されます(自分でテーブル定義を書くのは難しくないですけど、なるべく楽したいということです)..
- ソースコードが Ruby なので,機能の追加も難しくない(と思います).
【プログラムの機能】
- CSV ファイルを insert into 形式の SQL 文に変換する.同時に create table 文も生成する
- 各列のデータ型は CSV ファイルのデータの中身から推定. text を含む列は text, 全て integer である列は integer, 全て integer or real である列は real
- 列ごとにデータを見て,「not null」を付けてよさそうな列には「not null」も付ける
【使用法】
ruby file.rb HOGE.CSV T > HOGE.SQL
「file.rb」は Ruby プログラム・ソースコードのファイル名. 「HOGE.CSV」の部分は,CSV ファイル名を設定 (Windows の場合は「R:/hoge.csv」のような書き方をしてもよい). 「T」の部分はテーブル名の設定.
【プログラムのソースコードと実行結果の例】
- プログラムのソースコード: csvtosql.rb
#! 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 値あるいは空文字列(つまり「""」)のデータが最低1つ入っているような列は 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"
- 使ってみる
cd /tmp wget https://www.kkaneko.jp/cc/rubydb/csvtosql.rb wget https://www.kkaneko.jp/cc/rubydb/data.csv ruby csvtosql.rb data.csv T > data.sql cat data.csv cat data.sql
◆別の実行例