TXT TO SQL 変換７
戻る
<![CDATA[
# pro1.pl ファイルの全ての英小文字を英大文字に変換する
use strict;
my(%t);
open(IN,"../txt/p1.txt") or die "Can't open the file p1.txt.\n";
open(OUT,">../txt/p2.txt");
while(<IN>){
$t{line} = uc($_);
print OUT $t{line};
}
close(OUT);
close(IN);
--------------------------------------------------------------------
# pro2.pl, Ordering
use strict;
my(%t,@fld,$n,$n1);
open(IN,"../txt/p3.txt") or die "Can't open the file p3.txt.\n";
while(<IN>){
if ( $. == 1 ) {
$t{filename} = $_;
}
if ( /^PARTS/ ) {
chop;
@fld = split(/==>/);
push(@{ $t{name}{$fld[1]} }, $fld[2]);
$t{code}{$fld[2]} = $fld[3];
}
}
close(IN);
open(OUT,">../txt/p1.txt");
print OUT $t{filename};
for $n ( sort keys %{ $t{name} } ) {
%{ $t{list} } = (); # 初期化！
@{ $t{list1} } = ();
for $n1 ( 0 .. $#{ $t{name}{$n} } ) {
$t{list}{$t{name}{$n}[$n1]}++;
}
@{ $t{list1} } = sort keys %{ $t{list} };
for $n1 ( 0 .. $#{ $t{list1} } ) {
print OUT 'PARTS==>';
print OUT $n,'==>';
print OUT $t{list1}[$n1],'==>';
print OUT $t{code}{$t{list1}[$n1]},"\n";
}
}
close(OUT);
--------------------------------------------------------------------
# pro3.pl, 主機タイプの抽出とSQLファイル作成
use strict;
my(%t,@fld,$n,$n1);
open(IN,"../txt/p2.txt") or die "Can't open the file p2.txt.\n";
while(<IN>){
if ( /^PARTS/ ) {
@fld = split(/==>/);
$t{type}{$fld[1]}++;
if ( length($fld[1]) >= 100 ) {
print $.,'===>',$_;
exit;
}
}
}
close(IN);
@{ $t{types} } = sort keys %{ $t{type} };
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(100),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{types} } ) {
print OUT 'INSERT INTO main_type1 (name,memo) VALUES("';
print OUT $t{types}[$n],'","';
print OUT 'X");';
print OUT "\n";
}
print "The output file is ../sql/main_type1.sql\n";
--------------------------------------------------------------------
# pro4.pl parts1のテーブルを生成する
use strict;
my(%t,@fld,$n,$n1);
open(IN,"../txt/p2.txt") or die "Can't open the file p2.txt.\n";
while(<IN>){
if ( /^PARTS/ ) {
@fld = split(/==>/);
push(@{ $t{name}{$fld[1]} },$fld[2]);
push(@{ $t{code}{$fld[1]} },$fld[3]);
if ( length($fld[2]) >= 100 ) {
print $.,'===>',$_;
exit;
}
}
}
close(IN);
@{ $t{types} } = sort keys %{ $t{type} };
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(100),',"\n";
print OUT ' code CHAR(100),',"\n";
print OUT ' engine CHAR(200),',"\n";
print OUT ' DWG CHAR(200),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( sort keys %{ $t{name} } ) {
for $n1 ( 0 .. $#{ $t{name}{$n} } ) {
print OUT 'INSERT INTO parts1 (name,code,engine,DWG,memo) VALUES("';
print OUT $t{name}{$n}[$n1],'","';
print OUT $t{code}{$n}[$n1],'","';
print OUT $n,'","';
print OUT 'X","X");';
print OUT "\n";
}
}
print "The output file is ../sql/parts1.sql\n";
--------------------------------------------------------------------
c:\database\sql>mysql cookbook < parts1.sql -u cbuser -p
Enter password: ******
ERROR 1064 (42000) at line 3143: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to us
e near 'Y", WP210, 9A, L800MM, ONE END C3/8, ANOTHER END B3/8","F4-12704-08
","0","X","X' at line 1
--------------------------------------------------------------------
# pro5.pl, Ordering
use strict;
my(%t,@fld,$n,$n1);
open(IN,"../txt/e2.txt") or die "Can't open the file e2.txt.\n";
while(<IN>){
if ( $. == 1 ) {
$t{filename} = $_;
}
if ( /^ENGINE_MAKER/ ) {
chop;
@fld = split(/==>/);
$t{list}{$fld[1]}++;
}
}
close(IN);
@{ $t{list1} } = sort keys %{ $t{list} };
open(OUT,">../txt/e3.txt");
print OUT $t{filename};
for $n ( 0 .. $#{ $t{list1} } ) {
print OUT "ENGINE_MAKER==>";
print OUT $t{list1}[$n],"\n";
}
close(OUT);
--------------------------------------------------------------------
# pro6.pl, エンジンメーカーのSQL作成
use strict;
my(%t,@fld,$n,$n1);
open(IN,"../txt/e3.txt") or die "Can't open the file e3.txt.\n";
while(<IN>){
if ( $. == 1 ) {
$t{filename} = $_;
}
if ( /^ENGINE_MAKER/ ) {
chop;
@fld = split(/==>/);
push(@{ $t{list} },$fld[1]);
}
}
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(50),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{list} } ) {
print OUT 'INSERT INTO main_maker1 (name,memo) VALUES("';
print OUT $t{list}[$n],'","';
print OUT 'X");';
print OUT "\n";
}
print "The output file is ../sql/main_maker1.sql\n";
--------------------------------------------------------------------
# pro7.pl, Ordering
use strict;
my(%t,@fld,$n,$n1);
open(IN,"../txt/d2.txt") or die "Can't open the file d2.txt.\n";
while(<IN>){
if ( $. == 1 ) {
$t{filename} = $_;
}
if ( /^ENGINE_NAME/ ) {
chop;
@fld = split(/==>/);
$t{list}{$fld[1]}++;
}
}
close(IN);
@{ $t{list1} } = sort keys %{ $t{list} };
open(OUT,">../txt/d1.txt");
print OUT $t{filename};
for $n ( 0 .. $#{ $t{list1} } ) {
print OUT "ENGINE_NAME==>";
print OUT $t{list1}[$n],"\n";
}
close(OUT);
--------------------------------------------------------------------
# pro8.pl, 主機種類SQL作成
use strict;
my(%t,@fld,$n,$n1);
open(IN,"../txt/d1.txt") or die "Can't open the file d1.txt.\n";
while(<IN>){
if ( $. == 1 ) {
$t{filename} = $_;
}
if ( /^ENGINE_NAME/ ) {
chop;
@fld = split(/==>/);
push(@{ $t{list} },$fld[1]);
if ( length($fld[1]) >= 80 ) {
print $.,"\n";
exit;
}
}
}
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(80),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{list} } ) {
print OUT 'INSERT INTO main_name1 (name,memo) VALUES("';
print OUT $t{list}[$n],'","';
print OUT 'X");';
print OUT "\n";
}
print "The output file is ../sql/main_name1.sql\n";
--------------------------------------------------------------------
]]>
戻る