TXT TO SQL 変換６
戻る
<![CDATA[
# Partsを抽出,中間ファイルを作成
# get_parts1.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}/parts1.txt");
print OUT "Filename=parts1.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{engine} = 0;
for $n ( 1 .. $#{ $ref->[1]{cell}[2] } ) {
$t{XY1} = 'B' . $n;
$t{XY2} = 'C' . $n;
$t{one1} = $ref->[1]{$t{XY1}};
$t{one2} = $ref->[1]{$t{XY2}};
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}};
$t{flag} = 2;
if ( $t{one1} =~ /^TYPE/ ) {
$t{engine} = $t{one1};
} elsif ($t{one2} =~ /^TYPE/) {
$t{engine} = $t{one2};
} elsif ($t{one3} =~ /^TYPE/) {
$t{engine} = $t{one3};
}
}
$t{one2} = $ref->[1]{$t{XY2}};
if ( length($t{one1}) > 2 && length($t{one2}) > 2 ) {
# last if $t{engine} == 0;
print OUT "PARTS==>$t{engine}==>$t{one1}==>$t{one2}\n";
}
if ( $t{one1} =~ /Description/ ) {
$t{flag} = 1;
}
}
# ファイルをクローズ
undef $ref;
}
print "The output file is /$t{T_DIR}/parts1.txt\n";
__DATA__
C E_DIR T_DIR
SQL==>共同work==>txt
C START END
#RANGE==>1==>500
#RANGE==>501==>1000
RANGE==>1001==>1500
__END__;
---------------------------------------------------------------
# partsを整理
# get_parts2.pl
use strict;
my (%t,@fld,$n,$n1);
# Read input data
open(IN,"../txt/parts.txt");
while(<IN>){
chomp;
@fld = split(/==>/);
next unless $fld[0] eq 'PARTS';
$fld[1] =~ s/\"/\'/g;
$fld[2] =~ s/^\s*//;
$fld[2] =~ s/\s*$//;
$fld[2] =~ s/\"/\'/g;
$fld[3] =~ s/^\s*//;
$fld[3] =~ s/\s*$//;
$t{one1} = $fld[2] . '==>' . $fld[3];
push(@{ $t{parts}{$fld[1]} },$t{one1});
}
close(IN);
@{ $t{engines} } = sort keys %{ $t{parts} };
open(OUT,">../txt/parts_order.txt");
print OUT "filename=parts_order.txt\n";
for $n ( 0 .. $#{ $t{engines} } ) {
$t{engine1} = $t{engines}[$n];
% { $t{list} } = ();
for $n1 ( 0 .. $#{ $t{parts}{$t{engine1}} } ) {
$t{parts1} = $t{parts}{$t{engine1}}[$n1];
$t{list}{$t{parts1}}++;
}
@{ $t{ps} } = sort keys %{ $t{list} };
for $n1 ( 0 .. $#{ $t{ps} } ) {
$t{p1} = $t{ps}[$n1];
print OUT "PARTS==>$t{engine1}==>$t{p1}\n";
}
}
close(OUT);
print "The output file is ../txt/parts_order.txt\n";
__END__;
---------------------------------------------------------------
# EnginesのSQLファイルを作成
# get_parts3.pl
use strict;
my (%t,$n,@fld);
# Read input data
open(IN,"../txt/parts_order.txt") or die "Can't open the file parts_order.\n";
while(<IN>){
chop;
@fld = split(/==>/);
next unless $fld[0] eq 'PARTS';
push(@{ $t{engines} },$fld[1]);
push(@{ $t{names} },$fld[2]);
push(@{ $t{codes} },$fld[3]);
}
close(IN);
open(OUT,">../sql/parts1.sql");
print OUT 'DROP TABLE IF EXISTS parts1;',"\n";
print OUT 'CREATE TABLE parts1',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name CHAR(200),',"\n";
print OUT ' code CHAR(100),',"\n";
print OUT ' engine CHAR(200),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{names} } ) {
print OUT 'INSERT INTO parts1 (name,code,engine,memo) VALUES("';
print OUT $t{names}[$n],'","';
print OUT $t{codes}[$n],'","';
print OUT $t{engines}[$n],'","';
print OUT 'X");';
print OUT "\n";
}
print "The output file is ../sql/parts1.sql\n";
__END__;
]]>
戻る