insert文を生成する

csvファイル

$ more hr.txt
AR,Argentina,2
AU,Australia,3
BE,Belgium,1
BR,Brazil,2
...
ZM,Zambia,4
ZW,Zimbabwe,4

perl

(make_insert_st.pl)

#!/usr/bin/env perl

open(FILE,$ARGV[0]) || die "$ARGV[0] not found.";

while(<FILE>){

  chomp();
  @temp = split(/,/);
  for($i=0; $i<@temp; $i++){
    $temp[$i] = "'".$temp[$i]."'";
  }
 
  $values = join(",",@temp,);
  print "insert into TABLE_NAME values ($values);\n";

}

実行結果

$ ./make_insert_st.pl hr.txt 
insert into TABLE_NAME values ('AR','Argentina','2');
insert into TABLE_NAME values ('AU','Australia','3');
insert into TABLE_NAME values ('BE','Belgium','1');
insert into TABLE_NAME values ('BR','Brazil','2');
...
insert into TABLE_NAME values ('ZM','Zambia','4');
insert into TABLE_NAME values ('ZW','Zimbabwe','4');

map関数を使う

#!/usr/bin/env perl

open(FILE,$ARGV[0]) || die "$ARGV[0] not found.";

while(<FILE>){

  chomp();
  @temp = split(/,/);
  @temp2 =  map { "'".$_."'" } @temp;
  $values = join(",",@temp2);

  print "insert into TABLE_NAME values ($values);\n";

} 

close(FILE);

参照したURL: http://www.geocities.jp/ky_webid/perl5/035.html

ruby

#!/usr/bin/env ruby

f = File.open(ARGV[0])

f.each do |line|
  line = line.chomp
  temp = line.split(',')
  print "insert into TABLE_NAME values ("
  print temp.map{|el| "'"+el+"'"}.join(',')
  print ");\n"
end

f.close

excel

=CONCATENATE("insert into TABLE_NAME values('",A1,"','",B1,"','",C1,"');")