TXT TO SQL 変換２
戻る
<![CDATA[
c:\database\perl>perl make_makers.pl
Can't locate Spreadsheet/Read.pm in @INC (@INC contains: C:/Perl/lib C:/Perl/sit
e/lib .) at make_makers.pl line 3.
BEGIN failed--compilation aborted at make_makers.pl line 3.
c:\database\perl>ppm install spreadsheet-read
====================
Install 'Archive-Zip' version 1.20 in ActivePerl 5.8.4.810.
====================
Installing C:\Perl\html\site\lib\Archive\Zip.html
Installing C:\Perl\html\site\lib\Archive\Zip\FAQ.html
Installing C:\Perl\html\site\lib\Archive\Zip\MemberRead.html
Installing C:\Perl\html\site\lib\Archive\Zip\Tree.html
Installing C:\Perl\site\lib\Archive\Zip.pm
Installing C:\Perl\site\lib\Archive\Zip\Archive.pm
Installing C:\Perl\site\lib\Archive\Zip\BufferedFileHandle.pm
Installing C:\Perl\site\lib\Archive\Zip\DirectoryMember.pm
Installing C:\Perl\site\lib\Archive\Zip\FAQ.pod
Installing C:\Perl\site\lib\Archive\Zip\FileMember.pm
Installing C:\Perl\site\lib\Archive\Zip\Member.pm
Installing C:\Perl\site\lib\Archive\Zip\MemberRead.pm
Installing C:\Perl\site\lib\Archive\Zip\MockFileHandle.pm
Installing C:\Perl\site\lib\Archive\Zip\NewFileMember.pm
Installing C:\Perl\site\lib\Archive\Zip\StringMember.pm
Installing C:\Perl\site\lib\Archive\Zip\Tree.pm
Installing C:\Perl\site\lib\Archive\Zip\ZipFileMember.pm
Installing C:\Perl\bin\crc32
Installing C:\Perl\bin\crc32.bat
Successfully installed Archive-Zip version 1.20 in ActivePerl 5.8.4.810.
====================
Install 'Spreadsheet-ReadSXC' version 0.20 in ActivePerl 5.8.4.810.
====================
Installing C:\Perl\html\site\lib\Spreadsheet\ReadSXC.html
Installing C:\Perl\site\lib\Spreadsheet\ReadSXC.pm
Successfully installed Spreadsheet-ReadSXC version 0.20 in ActivePerl 5.8.4.810.
====================
Install 'OLE-Storage_Lite' version 0.14 in ActivePerl 5.8.4.810.
====================
Installing C:\Perl\html\site\lib\OLE\Storage_Lite.html
Installing C:\Perl\site\lib\OLE\Storage_Lite.pm
Successfully installed OLE-Storage_Lite version 0.14 in ActivePerl 5.8.4.810.
====================
Install 'Spreadsheet-ParseExcel' version 0.32 in ActivePerl 5.8.4.810.
====================
Installing C:\Perl\html\site\lib\Spreadsheet\ParseExcel.html
Installing C:\Perl\html\site\lib\Spreadsheet\ParseExcel\SaveParser.html
Installing C:\Perl\html\site\lib\Spreadsheet\ParseExcel\Utility.html
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel.pm
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel\Dump.pm
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel\FmtDefault.pm
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel\FmtJapan.pm
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel\FmtJapan2.pm
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel\FmtUnicode.pm
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel\SaveParser.pm
Installing C:\Perl\site\lib\Spreadsheet\ParseExcel\Utility.pm
Successfully installed Spreadsheet-ParseExcel version 0.32 in ActivePerl 5.8.4.8
10.
====================
Install 'spreadsheet-read' version 0.03 in ActivePerl 5.8.4.810.
====================
Installing C:\Perl\html\site\lib\Spreadsheet\Read.html
Installing C:\Perl\site\lib\Spreadsheet\Read.pm
Successfully installed spreadsheet-read version 0.03 in ActivePerl 5.8.4.810.
---------------------------------------------------------------
C:\database\sql>mysql cookbook < makers.sql -u cbuser -p
Enter password: ******
ERROR 1406 (22001) at line 18: Data too long for column 'memo' at row 1
---------------------------------------------------------------
C:\database\sql>mysql cookbook < makers.sql -u cbuser -p
Enter password: ******
ERROR 1366 (HY000) at line 73: Incorrect string value: '\x82s\x82d\x82k...' for
column 'telfax' at row 1
---------------------------------------------------------------
# make_makers.pl
use strict;
use Spreadsheet::Read;
use Encode qw(from_to);
my(%t,$n,$ref,$cell);
$t{file} = 'makers.xls';
# ファイルをオープン
$ref = ReadData("../input/$t{file}") or die "Can't open the file $t{file}\n";
# B列のすべてのデータを読む
for $n ( 1 .. $#{ $ref->[1]{cell}[2] } ) {
$t{XY1} = 'B' . $n;
$t{XY2} = 'E' . $n;
$t{one1} = $ref->[1]{$t{XY1}};
$t{one2} = $ref->[1]{$t{XY2}};
if (length($t{one1}) < 2 ) {
$t{NO}=0;
$t{Elist}='';
next;
}
$t{one1} = Encode::encode("shiftjis",$t{one1});
$t{one2} = Encode::encode("shiftjis",$t{one2});
$t{NO}++;
if ( $t{NO} == 1 ) {
push(@{ $t{TO} },$t{one1});
if ( length($t{one2}) > 2 ) {
$t{Elist} = $t{one2};
}
} elsif ( $t{NO} == 2 ) {
push(@{ $t{ATTN} },$t{one1});
if ( length($t{one2}) > 2 ) {
$t{Elist} = $t{Elist} . ',' . $t{one2};
}
} elsif ( $t{NO} == 3 ) {
push(@{ $t{TEL} },$t{one1});
if ( length($t{one2}) > 2 ) {
$t{Elist} = $t{Elist} . ',' . $t{one2};
}
push(@{ $t{Es} }, $t{Elist});
}
}
$t{sanma}='様';
# テスト用
#open(OUT1,">../sql/makerstmp.txt");
#for $n ( 0 .. $#{ $t{TO} } ) {
# $t{Memo} = $t{Es}[$n];
# print OUT1 '"';
# print OUT1 $t{Memo},'"';
# print OUT1 "\n";
#}
#close(OUT1);
#exit;
# Write to a sql file.
open(OUT,">../sql/makers.sql");
print OUT 'DROP TABLE IF EXISTS makers;',"\n";
print OUT 'CREATE TABLE makers', "\n";
print OUT '(',"\n";
print OUT "\t",'id INT AUTO_INCREMENT,',"\n";
print OUT "\t",'company CHAR(100),',"\n";
print OUT "\t",'address CHAR(100),',"\n";
print OUT "\t",'person CHAR(50),',"\n";
print OUT "\t",'telfax CHAR(100),',"\n";
print OUT "\t",'email CHAR(100),',"\n";
print OUT "\t",'homepage CHAR(100),',"\n";
#print OUT "\t",'memo CHAR(100),',"\n";
print OUT "\t",'memo CHAR(200),',"\n";
print OUT "\t",'PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
for $n ( 0 .. $#{ $t{TO} } ) {
$t{company} = $t{TO}[$n];
$t{company} =~ s/TO:\s*//;
from_to($t{company},"shiftjis","utf8");
$t{address} = 'XXX';
$t{email} = 'XXX';
$t{homepage} = 'XXX';
$t{person} = $t{ATTN}[$n];
$t{person} =~ s/ＡＴＴＮ：\s*//;
# $t{name} =~ s/$t{sanma}//g;
$t{person} =~ s/\s*$//;
$t{person} =~ s/^\s*//;
from_to($t{person},"shiftjis","utf8");
$t{telfax} = $t{TEL}[$n];
from_to($t{tel},"shiftjis","utf8");
$t{memo} = $t{Es}[$n];
from_to($t{memo},"shiftjis","utf8");
print OUT 'INSERT INTO makers ';
print OUT '(company,address,person,telfax,email,homepage,memo) VALUES("';
print OUT $t{company},'","';
print OUT $t{address},'","';
print OUT $t{person},'","';
print OUT $t{telfax},'","';
print OUT $t{email},'","';
print OUT $t{homepage},'","';
print OUT $t{memo};
print OUT '");',"\n";
}
close(OUT);
]]>
戻る