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(<IN>){ 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(<IN>){ 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(<IN>){ 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"; --------------------------------------------------------------------------------
戻る