TXT TO SQL 変換５
戻る
<![CDATA[
# Partsを抽出,中間ファイルを作成
# get_engines1.pl
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}/engines1.txt");
print OUT "Filename=engines1.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列のすべてのデータを読む
print "NO==>$n,file=$t{file1}\n";
$t{flag} = 0;
for $n ( 1 .. $#{ $ref->[1]{cell}[2] } ) {
$t{XY1} = 'B' . $n;
$t{one1} = $ref->[1]{$t{XY1}};
if ( $t{flag} == 1 && length($t{one1}) > 2 ) {
$t{N1} = $n;
$t{XY1} = 'B' . $t{N1};
$t{one1} = $ref->[1]{$t{XY1}};
$t{N1} = $n + 1;
$t{XY1} = 'B' . $t{N1};
$t{one2} = $ref->[1]{$t{XY1}};
$t{N1} = $n + 2;
$t{XY1} = 'B' . $t{N1};
$t{one3} = $ref->[1]{$t{XY1}};
print OUT "file==>$t{file1}==>engine1==>$t{one1}\n";
print OUT "file==>$t{file1}==>engine2==>$t{one2}\n";
print OUT "file==>$t{file1}==>engine3==>$t{one3}\n";
last;
}
if ( $t{one1} =~ /Description/ ) {
$t{flag} = 1;
}
}
# ファイルをクローズ
undef $ref;
}
print "The output file is /$t{T_DIR}/engines1.txt\n";
__DATA__
C E_DIR T_DIR
SQL==>共同work==>txt
C START END
#RANGE==>1==>50
RANGE==>50==>2000
__END__;
---------------------------------------------------------------
# enginesを整理
# get_engines2.pl
use strict;
my (%t,@fld,$n);
# Read input data
open(IN,"../txt/engines.txt");
while(<IN>){
chomp;
@fld = split(/==>/);
if ( $fld[2] eq "engine1" ) {
$t{engines1}{$fld[3]}++;
} elsif ( $fld[2] eq "engine2" ) {
$t{engines2}{$fld[3]}++;
} elsif ( $fld[2] eq "engine3" ) {
$t{engines3}{$fld[3]}++;
}
}
close(IN);
# エンジン名を処理
@{ $t{engine_name} } = sort keys %{ $t{engines1} };
open(OUT,">../txt/engine_name.txt");
print OUT "filename=engine_name.txt\n";
for $n ( 0 .. $#{ $t{engine_name} } ) {
print OUT "ENGINE_NAME==>$t{engine_name}[$n]\n";
}
close(OUT);
print "The output file is ../txt/engine_name.txt\n";
__END__;
# エンジンメーカーを処理
@{ $t{engine_maker} } = sort keys %{ $t{engines3} };
open(OUT,">../txt/engine_maker.txt");
print OUT "filename=engine_maker.txt\n";
for $n ( 0 .. $#{ $t{engine_maker} } ) {
print OUT "ENGINE_MAKER==>$t{engine_maker}[$n]\n";
}
close(OUT);
print "The output file is ../txt/engine_maker.txt\n";
__END__;
# エンジンタイプを処理
@{ $t{engine_type} } = sort keys %{ $t{engines2} };
open(OUT,">../txt/engine_type.txt");
print OUT "filename=engine_type.txt\n";
for $n ( 0 .. $#{ $t{engine_type} } ) {
print OUT "ENGINE_TYPE==>$t{engine_type}[$n]\n";
}
close(OUT);
print "The output file is ../txt/engine_type.txt\n";
---------------------------------------------------------------
# EnginesのSQLファイルを作成
# get_engines3.pl
use strict;
my (%t,$n,@fld);
# Read input data
open(IN,"../txt/engine_maker.txt") or die "Can't open the file engine_maker.txt.\n";
while(<IN>){
chop;
@fld = split(/==>/);
$_ = $fld[1];
s/^\s*//;
s/\"/\'/g;
next if length($_) == 0;
next unless $_ =~ /^M/;
push(@{ $t{names} },$_);
}
close(IN);
open(OUT,">../sql/main_maker1.sql");
print OUT 'DROP TABLE IF EXISTS main_maker1;',"\n";
print OUT 'CREATE TABLE main_maker1',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name CHAR(200),',"\n";
print OUT ' memo CHAR(100),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{names} } ) {
print OUT 'INSERT INTO main_maker1 (name,memo) VALUES("';
print OUT $t{names}[$n],'","';
print OUT 'X");';
print OUT "\n";
}
print "The output file is ../sql/main_maker1.sql\n";
__END__;
# Read input data
open(IN,"../txt/engine_type.txt") or die "Can't open the file engine_type.txt.\n";
while(<IN>){
chop;
@fld = split(/==>/);
$_ = $fld[1];
s/^\s*//;
s/\"/\'/g;
next if length($_) == 0;
next unless $_ =~ /^TYPE/;
push(@{ $t{names} },$_);
}
close(IN);
open(OUT,">../sql/main_type1.sql");
print OUT 'DROP TABLE IF EXISTS main_type1;',"\n";
print OUT 'CREATE TABLE main_type1',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name CHAR(200),',"\n";
print OUT ' memo CHAR(100),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{names} } ) {
print OUT 'INSERT INTO main_type1 (name,memo) VALUES("';
print OUT $t{names}[$n],'","';
print OUT 'X");';
print OUT "\n";
}
print "The output file is ../sql/main_type1.sql\n";
__END__;
# Read input data
open(IN,"../txt/engine_name.txt") or die "Can't open the file engine_name.txt.\n";
while(<IN>){
chop;
@fld = split(/==>/);
$_ = $fld[1];
s/^\s*//;
next if length($_) == 0;
push(@{ $t{names} },$_);
push(@{ $t{types} },1);
}
close(IN);
open(OUT,">../sql/main_name1.sql");
print OUT 'DROP TABLE IF EXISTS main_name1;',"\n";
print OUT 'CREATE TABLE main_name1',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name CHAR(200),',"\n";
print OUT ' type CHAR(10),',"\n";
print OUT ' memo CHAR(100),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{names} } ) {
print OUT 'INSERT INTO main_name1 (name,type,memo) VALUES("';
print OUT $t{names}[$n],'","';
print OUT $t{types}[$n],'","';
print OUT 'X");';
print OUT "\n";
}
print "The output file is ../sql/main_name1.sql\n";
]]>
戻る