TXT TO SQL 変換４
戻る
<![CDATA[
# HULL_NOを抽出,中間ファイルを作成
# get_hullno1.pl 2007.10.08
use strict;
use Spreadsheet::Read;
my (%t,$n,@fld,$ref);
# Read setting data
while(<DATA>){
chomp;
@fld = split(/==>/);
if ( /^SQL/ ) {
$t{E_DIR} = $fld[1];
$t{T_DIR} = $fld[2];
} elsif ( /^RANGE/ ) {
$t{START} = $fld[1];
$t{END} = $fld[2];
}
}
close(DATA);
$t{inputf} = $t{E_DIR} . '-files_all.txt';
# ファイル名ファイルを読む
open(IN,"../$t{T_DIR}/$t{inputf}") or die "Can't open the file $t{inputf}\n";
$t{NO}=0;
while(<IN>){
if ( /^FILE/ ) {
chomp;
@fld = split(/==>/);
$t{NO}++;
if ( $t{NO} >= $t{START} ) {
push(@{ $t{files} },$fld[1]);
}
last if $t{NO} >= $t{END};
}
}
close(IN);
open(OUT,">../$t{T_DIR}/hullnos1.txt");
print OUT "Filename=hullnos1.txt.\n";
# ひとつずつ処理
for $n ( 0 .. $#{ $t{files} } ) {
$t{file1} = $t{files}[$n];
$ref = ReadData("../$t{E_DIR}/$t{file1}") or die "Can't open the file $t{E_DIR}/$t{file1}\n";
# B列のすべてのデータを読む
for $n ( 1 .. $#{ $ref->[1]{cell}[2] } ) {
$t{XY1} = 'B' . $n;
$t{one1} = $ref->[1]{$t{XY1}};
if ( $t{one1} =~ /^HULL/ ) {
$t{n1} = $n - 1;
$t{XY1} = 'B' . $t{n1};
$t{one1} = $ref->[1]{$t{XY1}};
$t{n2} = $n;
$t{XY1} = 'B' . $t{n2};
$t{one2} = $ref->[1]{$t{XY1}};
$t{n3} = $n + 1;
$t{XY1} = 'B' . $t{n3};
$t{one3} = $ref->[1]{$t{XY1}};
print OUT 'FILE==>',$t{file1};
print OUT '==>',$t{one1};
print OUT '==>',$t{one2};
print OUT '==>',$t{one3};
print OUT "\n";
last;
}
}
# ファイルをクローズ
undef $ref;
}
print "The output file is /$t{T_DIR}/hullnos1.txt\n";
__DATA__
C E_DIR T_DIR
SQL==>共同work==>txt
C START END
RANGE==>1==>50
__END__;
---------------------------------------------------------------
# HULL_NOを整理
# get_hullno2.pl 2007.10.08
use strict;
my (%t,@fld,$n);
# Read input data
open(IN,"../txt/hullnos.txt");
while(<IN>){
chomp;
@fld = split(/==>/);
if ( /^FILE/ ) {
$t{hulls}{$fld[3]} = $fld[2];
}
}
close(IN);
# すべてのhullnosを抽出
@{ $t{hull_uniq_key} } = sort keys %{ $t{hulls} };
# 中間ファイルにセーブ
open(OUT,">../txt/hulls.txt");
for $n ( 0 .. $#{ $t{hull_uniq_key} } ) {
print OUT "$t{hull_uniq_key}[$n]==>$t{hulls}{$t{hull_uniq_key}[$n]}\n";
}
close(OUT);
print "The output file is hulls.txt\n";
__END__;
---------------------------------------------------------------
# HULL_NOのSQLファイルを作成
# get_hullno3.pl 2007.10.08
use strict;
my (%t,$n,@fld);
# Read input data
open(IN,"../txt/hulls.txt") or die "Can't open the file hullx.txt.\n";
while(<IN>){
chop;
@fld = split(/==>/);
$_ = $fld[0];
s/HULL NO//;
s/\://;
s/\.//;
s/^\s*//;
next if length($_) == 0;
$fld[1] =~ s/^\s*//;
push(@{ $t{hulls} },$_);
push(@{ $t{names} },$fld[1]);
}
close(IN);
open(OUT,">../sql/hulls.sql");
print OUT 'DROP TABLE IF EXISTS hull_no;',"\n";
print OUT 'CREATE TABLE hull_no',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' HULL_NO CHAR(50),',"\n";
print OUT ' name CHAR(50),',"\n";
print OUT ' built DATE,',"\n";
print OUT ' flag CHAR(50),',"\n";
print OUT ' memo CHAR(100),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{hulls} } ) {
print OUT 'INSERT INTO hull_no (HULL_NO,name,built,flag,memo) VALUES("';
print OUT $t{hulls}[$n],'","';
print OUT $t{names}[$n],'","';
print OUT '0000-01-01","X","X");';
print OUT "\n";
}
print "The output file is ../sql/hulls.sql\n";
__END__;
]]>
戻る