- 昔々あるところにExcelで"入,３F,000000004101,hogehoge,男,076,才,2016.5.1,5:24,ＡＳＴ,2292"みたいな行が延々ならんでいるクソデータがありました しかも検査項目名が全角野郎でした
- それを下記のデータベースに落とし込みたいとおもいました
CREATE TABLE `Labo2016` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Department` varchar(20) DEFAULT NULL,
`PatID` varchar(13) DEFAULT NULL,
`PatName` varchar(30) DEFAULT NULL,
`PatAge` int(11) DEFAULT NULL,
`PatSex` varchar(4) DEFAULT NULL,
`TestDate` varchar(12) DEFAULT NULL,
`AST` float DEFAULT NULL,
`ALT` float DEFAULT NULL,
`LDH` float DEFAULT NULL,
`Na` float DEFAULT NULL,
`K` float DEFAULT NULL,
`BUN` float DEFAULT NULL,
`Cr` float DEFAULT NULL,
`BNP` float DEFAULT NULL,
`NTproBNP` float DEFAULT NULL,
`WBC` float DEFAULT NULL,
`Hb` float DEFAULT NULL,
`Ht` float DEFAULT NULL,
`Plt` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2982 DEFAULT CHARSET=utf8;
- おじいさんはCPANでinstall Spreadsheet::ParseExcelをした後、下記のようなperlを組みました
#!/usr/bin/perl -w
use Spreadsheet::ParseExcel;
use strict 'refs';
use utf8;
use Encode;
use DBI;
binmode STDOUT, ":utf8";
binmode STDIN, ":utf8";
{
my $FileName = $ARGV[0];;
my $ExcelObj = Spreadsheet::ParseExcel->new();
my $Book = $ExcelObj->parse($FileName);
our $DB_NAME = "databasename";
our $DB_USER = "username";
our $DB_PASS = "password";
$dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME","$DB_USER","$DB_PASS") or die "$!\n Error: failed to connect to DB.\n";
for my $Sheet ($Book->worksheets()) {
&GetValuesFromSheet($Sheet);
last;
}
$dbh->disconnect;
}
sub updateDB {
my ($patID, $testDate, $item, $value) = @_;
$itemCol = $item;
$itemCol =~ tr/０-９Ａ-Ｚａ-ｚ/0-9A-Za-z/;
$itemCol =~ s/HCT/Ht/;
$itemCol =~ s/HGB/Hb/;
$itemCol =~ s/PLT/Plt/;
$itemCol =~ s/CRE/Cr/;
$sql = sprintf("UPDATE Labo2016 SET %s=? WHERE PatID=? AND TestDate=?", $itemCol);
$sth = $dbh->prepare($sql);
$sth->execute($value,$patID,$testDate);
}
sub GetValuesFromSheet {
my ($Sheet) = @_;
my ($Rmin, $Rmax) = $Sheet->row_range();
my ($Cmin, $Cmax) = $Sheet->col_range();
for($i=$Rmin+1;$i<$Rmax;$i++)
{
$cell = $Sheet->get_cell($i, 2);
if (defined($cell))
{
$patID = substr($cell->value(),2);
$cell = $Sheet->get_cell($i, 7);
$testDate = $cell->value();
$rc = $dbh->do("SELECT PatName FROM Labo2016 WHERE PatID=? AND TestDate=?",undef,$patID,$testDate);
if($rc eq "0E0")
{
$cell = $Sheet->get_cell($i, 1);
$patDep = $cell->value();
$cell = $Sheet->get_cell($i, 3);
$patName = $cell->value();
$cell = $Sheet->get_cell($i, 4);
$patSex = $cell->value();
$cell = $Sheet->get_cell($i, 5);
$patAge = $cell->value();
$sql = "INSERT INTO Labo2016 (`Department`, `PatID`, `PatName`, `PatAge`, `PatSex`, `TestDate`) ";
$sql = $sql . "VALUES (?, ?, ?, ?, ?, ?)";
$sth = $dbh->prepare($sql);
$sth->execute($patDep, $patID, $patName, $patAge,$patSex, $testDate);
$cell = $Sheet->get_cell($i, 9);
$itemName = $cell->value();
$cell = $Sheet->get_cell($i, 10);
$itemValue = $cell->value();
&updateDB($patID, $testDate, $itemName, $itemValue);
}
else
{
$cell = $Sheet->get_cell($i, 9);
$itemName = $cell->value();
$cell = $Sheet->get_cell($i, 10);
$itemValue = $cell->value();
&updateDB($patID, $testDate, $itemName, $itemValue);
}
}
}
}
