TXT TO SQL 変換３
戻る
<![CDATA[
# すべてのOURREFを抽出,SQLファイルを生成
# get_ourref.pl 2007.10.08
use strict;
my (%t,$s_ref,$n,$n1,@fld,$file1);
# Read setting data
while(<DATA>){
if ( /^SQL/ ) {
chomp;
@fld = split(/==>/);
$$s_ref{E_DIR} = $fld[1];
$$s_ref{T_DIR} = $fld[2];
}
}
close(DATA);
# Do one by one
($s_ref) = sfiles($s_ref);
# Making TXT file
$t{file1} = 'ourrefs.sql';
$t{txt_file} = '../sql/' . $t{file1};
open(OUT,"> $t{txt_file}");
print "The output file is $t{txt_file}\n";
print OUT 'DROP TABLE IF EXISTS our_ref;',"\n";
print OUT 'CREATE TABLE our_ref',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' ourref CHAR(40),',"\n";
print OUT ' states CHAR(20),',"\n";
print OUT ' memo CHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n1 ( 0 .. $#{ $$s_ref{files} } ) {
print OUT 'INSERT INTO our_ref (ourref,states) VALUES("';
print OUT $$s_ref{refs}[$n1],'",';
print OUT '"0");';
print OUT "\n";
}
close(OUT);
sub sfiles {
my($s_ref) = @_;
my (%t);
$t{Dir} = '../' . $$s_ref{E_DIR};
# Open the directory
opendir(BIN,$t{Dir}) or die "Can't open $t{Dir}.\n";
# Deal with files one by one
while (defined ($t{file} = readdir BIN) ) {
next if $t{file} =~ /^\.\.?$/;
if ( $t{file} =~ /^ENQ\s(\w*)\s/ ) {
if ( length($1) >= 7 ) {
push(@{ $$s_ref{files} },$t{file});
push(@{ $$s_ref{refs} },$1);
}
}
}
closedir(BIN);
return($s_ref);
}
__DATA__
C E_DIR T_DIR
SQL==>共同work==>txt
--------------
DROP TABLE IF EXISTS our_ref
ERROR:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the ma
nual that corresponds to your MySQL server version for the right syntax to use n
ear 'CREATE TABLE our_ref
(
id INT AUTO_INCREMENT,
ourref CHAR(40),
states' at line 2
--------------
# すべてのHULL_NOを抽出,SQLファイルを生成
# get_hullno.pl 2007.10.08
use strict;
my (%t,$s_ref,$n,$n1,@fld,$file1);
# Read setting data
while(<DATA>){
if ( /^SQL/ ) {
chomp;
@fld = split(/==>/);
$$s_ref{E_DIR} = $fld[1];
$$s_ref{T_DIR} = $fld[2];
}
}
close(DATA);
# Do one by one
($s_ref) = sfiles($s_ref);
# Making TXT file
$t{file1} = 'hullnos.sql';
$t{txt_file} = '../sql/' . $t{file1};
print "The output file is $t{txt_file}\n";
open(OUT,"> $t{txt_file}");
print OUT 'DROP TABLE IF EXISTS hull_no;',"\n";
print OUT 'CREATE TABLE hull_no',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' HULL_NO CHAR(50),',"\n";
print OUT ' name CHAR(50),',"\n";
print OUT ' built DATE,',"\n";
print OUT ' flag CHAR(50),',"\n";
print OUT ' memo CHAR(100),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n1 ( 0 .. $#{ $$s_ref{hullnos} } ) {
# print OUT 'INSERT INTO our_ref (ourref,states,memo) VALUES("';
# print OUT $$s_ref{refs}[$n1],'",';
# print OUT '"0","0");';
print OUT "$n1==>$$s_ref{hullnos}[$n1]";
print OUT "\n";
}
close(OUT);
sub sfiles {
my($s_ref) = @_;
my (%t,$ref,$n);
use Spreadsheet::Read;
$t{Dir} = '../' . $$s_ref{E_DIR};
# Open the directory
opendir(BIN,$t{Dir}) or die "Can't open $t{Dir}.\n";
# Deal with files one by one
$t{NO} = 0;
while (defined ($t{file} = readdir BIN) ) {
next if $t{file} =~ /^\.\.?$/;
if ( $t{file} =~ /^ENQ\s(\w*)\s/ ) {
# HULL_NOを読む
# ファイルをオープン
$t{NO}++;
$ref = ReadData("$t{Dir}/$t{file}") or die "Can't open the file $t{Dir}/$t{file}\n";
# B列のすべてのデータを読む
for $n ( 1 .. $#{ $ref->[1]{cell}[2] } ) {
$t{XY1} = 'B' . $n;
$t{one1} = $ref->[1]{$t{XY1}};
if ( $t{one1} =~ /^HULL/ ) {
push(@{ $$s_ref{hullnos} },$t{one1});
last;
}
}
# ファイルをクローズ
undef $ref;
print "NO====>$t{NO}\n";
}
}
closedir(BIN);
return($s_ref);
}
__DATA__
C E_DIR T_DIR
SQL==>共同work==>txt
--------------
Use of uninitialized value in addition (+) at C:/Perl/site/lib/Spreadsheet/Read.
pm line 74.
Use of uninitialized value in addition (+) at C:/Perl/site/lib/Spreadsheet/Read.
pm line 74.
Use of uninitialized value in foreach loop entry at C:/Perl/site/lib/Spreadsheet
/Read.pm line 82.
Use of uninitialized value in foreach loop entry at C:/Perl/site/lib/Spreadsheet
/Read.pm line 83.
Use of uninitialized value in foreach loop entry at C:/Perl/site/lib/Spreadsheet
/Read.pm line 83.
Use of uninitialized value in addition (+) at C:/Perl/site/lib/Spreadsheet/Read.
pm line 74.
Use of uninitialized value in addition (+) at C:/Perl/site/lib/Spreadsheet/Read.
pm line 74.
Use of uninitialized value in foreach loop entry at C:/Perl/site/lib/Spreadsheet
/Read.pm line 82.
Use of uninitialized value in foreach loop entry at C:/Perl/site/lib/Spreadsheet
/Read.pm line 83.
Use of uninitialized value in foreach loop entry at C:/Perl/site/lib/Spreadsheet
/Read.pm line 83.
]]>
戻る