|
<![CDATA[
# Transfer Excel data to sql file
# excel2sql.pl 2007.07.28
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use File::Copy;
$Win32::OLE::Warn = 3; # die on errors..
my (%t,$s_ref,$n,$n1,@fld);
# Read setting data
open(IN,"set.txt") || die "Can't open the file set.txt.\n";
while(<IN>){
last if (/^LAST/);
@fld = split;
if ( /^SQL/ ) {
push(@{ $$s_ref{NAME} }, $fld[1]);
push(@{ $$s_ref{DO} }, $fld[2]);
push(@{ $$s_ref{NEW} }, $fld[3]);
push(@{ $$s_ref{E_DIR} }, $fld[4]);
push(@{ $$s_ref{S_DIR} }, $fld[5]);
}
}
close(IN);
# Do one by one
for $n ( 0 .. $#{ $$s_ref{DO} } ) {
# Obtaining data from Excel files
$$s_ref{NAME1} = $$s_ref{NAME}[$n];
$$s_ref{E_DIR1} = $$s_ref{E_DIR}[$n];
($s_ref) = g_excel($s_ref);
# Making SQL file
$t{sql_file} = lc($$s_ref{NAME1}) . '.sql';
$t{sql_file} = '../sql/' . $t{sql_file};
open(OUT,"> $t{sql_file}");
if ( $$s_ref{NEW}[$n] ) {
($s_ref) = write_sqlheader($s_ref);
}
for $n1 ( 0 .. $#{ $$s_ref{hu_nos} } ) {
print OUT 'INSERT INTO ',$$s_ref{NAME1};
print OUT ' (name) VALUES(';
print OUT "'",$$s_ref{hu_nos}[$n1],"'";
print OUT ');',"\n";
}
close(OUT);
exit;
}
sub write_sqlheader {
my($s_ref) = @_;
print OUT 'DROP TABLE IF EXISTS ';
print OUT $$s_ref{NAME1},';',"\n";
print OUT 'CREATE TABLE ';
print OUT $$s_ref{NAME1},"\n";
print OUT '(',"\n";
print OUT "\t",'id INT AUTO_INCREMENT,', "\n";
print OUT "\t",'name VARCHAR(40),', "\n";
print OUT "\t",'PRIMARY KEY (id)', "\n";
print OUT ');',"\n\n";
return($s_ref);
}
sub g_excel {
my($s_ref) = @_;
my (%t,$n,$n1,$array,$Excel,$Book,$Sheet,$ref_array,$scalar,@fld);
$t{Dir} = '../' . $$s_ref{E_DIR1};
$Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application','Quit');
# 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/ ) {
$t{file1} = $t{Dir} . '/' . $t{file};
copy($t{file1},"tmp.xls"); # 漢字ファイル名対策
$Book = $Excel->Workbooks->Open("C:\\database\\perl\\tmp.xls");
$Sheet = $Book->Worksheets(1);
$array = $Sheet->Range("B14:B16")->{'Value'};
$Book->Close;
# print "file=$t{file}\n";
foreach $ref_array (@$array) {
foreach $scalar (@$ref_array) {
if ( $scalar =~ /^HULL/ ) {
$t{hull1}{$scalar}++;
}
}
# print "\n";
}
}
}
closedir(BIN);
undef $Excel;
# List hull no:
@{ $t{hulls} } = keys %{ $t{hull1} };
for $n ( 0 .. $#{ $t{hulls} } ) {
$_ = $t{hulls}[$n];
@fld = split;
$t{hu1} = $fld[2];
for $n1 ( 3 .. $#fld ) {
$t{hu1} = $t{hu1} . ' ' . $fld[$n1];
}
push(@{ $$s_ref{hu_nos} },$t{hu1});
};
return($s_ref);
}
__END__
(
id INT AUTO_INCREMENT,
name VARCHAR(40),
PRIMARY KEY (id)
)
ERROR 1064 (42000) at line 2: 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 'INSERT INTO hull_no (name) VALUES('SOTODA SHIPBUILDING 237')' at line 7
(
id INT AUTO_INCREMENT,
name VARCHAR(40),
PRIMARY KEY (id)
);
]]>
|