TXT TO SQL 変換9
戻る
# parts_order.txtファイルのOrdering
# make_parts_order.pl
use strict;
use File::Copy;
my (%t,$n,$n1,@fld,@list);
copy("../txt/parts_order.txt","../txt/parts_order_tmp.txt");
$t{inputfile} = 'parts_order_tmp.txt';
# Reading input data
open(IN,"../txt/$t{inputfile}") or die "Can't open the file $t{inputfile}.\n";
while(){
if ( $. == 1 ) {
$t{filename} = $_;
next;
}
if (/^PARTS/) {
chop;
@fld = split(/==>/);
next until $fld[2];
push(@{ $t{code}{$fld[1]} },$fld[3]);
$t{code_name}{$fld[3]} = $fld[2];
}
}
close(IN);
$t{outputfile} = 'parts_order.txt';
open(OUT,">../txt/$t{outputfile}");
print OUT $t{filename};
for $n ( sort keys %{ $t{code} } ) {
%{ $t{code_tmp} } = ();
for $n1 ( @{ $t{code}{$n} } ) {
$t{code_tmp}{$n1}++;
}
@{ $t{code_order} } = sort keys %{ $t{code_tmp} };
for $n1 (0..$#{ $t{code_order} }) {
print OUT "PARTS==>";
print OUT $n,"==>";
print OUT $t{code_name}{$t{code_order}[$n1]},"==>";
print OUT $t{code_order}[$n1],"\n";
}
}
print "The output file is ../txt/$t{outputfile}.\n";
--------------------------------------------------------------------------------
# SQLファイルを生成(main_type1)
# make_parts_type1.pl
use strict;
my (%t,$n,@fld,@list);
$t{inputfile} = 'parts_order.txt';
# Reading input data
open(IN,"../txt/$t{inputfile}") or die "Can't open the file $t{inputfile}.\n";
while(){
if (/^PARTS/) {
chop;
@fld = split(/==>/);
next until $fld[2];
if ( length($fld[1]) >= 50 ) {
print $.,'==>',$_;
exit;
}
$t{list}{$fld[1]}++;
}
}
close(IN);
@list = sort keys %{ $t{list} };
$t{table} = 'main_type1';
$t{outputfile} = $t{table} . '.sql';
open(OUT,">../sql/$t{outputfile}");
print OUT 'DROP TABLE IF EXISTS ';
print OUT $t{table},';',"\n";
print OUT 'CREATE TABLE ';
print OUT $t{table},"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name CHAR(50),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#list ) {
print OUT 'INSERT INTO ';
print OUT $t{table};
print OUT ' (name,memo) VALUES("';
print OUT $list[$n],'","X");';
print OUT "\n";
}
print "The output file is ../txt/$t{outputfile}.\n";
--------------------------------------------------------------------------------
# SQLファイルを生成(parts1)
# make_parts_parts1.pl
use strict;
my (%t,$n,$n1,@fld,@list);
$t{inputfile} = 'parts_order.txt';
# Reading input data
open(IN,"../txt/$t{inputfile}") or die "Can't open the file $t{inputfile}.\n";
while(){
if (/^PARTS/) {
chop;
@fld = split(/==>/);
next until $fld[2];
if ( length($fld[1]) >= 50 ) {
print $.,'==>',$_;
exit;
}
push(@{ $t{name}{$fld[1]} },$fld[2]);
push(@{ $t{code}{$fld[1]} },$fld[3]);
if ( length($fld[2]) >= 100 or length{$fld[3]} >= 50) {
print $.,"==>",$_,"\n";
exit;
}
}
}
close(IN);
$t{table} = 'parts1';
$t{outputfile} = $t{table} . '.sql';
open(OUT,">../sql/$t{outputfile}");
print OUT 'DROP TABLE IF EXISTS ';
print OUT $t{table},';',"\n";
print OUT 'CREATE TABLE ';
print OUT $t{table},"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name CHAR(100),',"\n";
print OUT ' code CHAR(50),',"\n";
print OUT ' engine INT,',"\n";
print OUT ' dwg INT,',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
$t{NO} = 0;
for $n ( sort keys %{ $t{name} } ) {
$t{NO}++;
for $n1 ( 0 .. $#{ $t{name}{$n} } ) {
print OUT 'INSERT INTO ';
print OUT $t{table};
print OUT ' (name,code,engine,dwg,memo) VALUES("';
print OUT $t{name}{$n}[$n1],'","';
print OUT $t{code}{$n}[$n1],'","';
print OUT $t{NO},'",1,"X");';
print OUT "\n";
}
}
print "The output file is ../sql/$t{outputfile}.\n";
--------------------------------------------------------------------------------
戻る