gikoha’s blog

個人的メモがわり

クソデータの処理

  • 昔々あるところに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];;                     # Excel(xls)ファイル名
    my $ExcelObj = Spreadsheet::ParseExcel->new(); # ParseExcelのオブジェクト定義
    
    my $Book = $ExcelObj->parse($FileName);        # xlsファイル読み込み/book扱い

	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()) {          # worksheetオブジェクト取得
        &GetValuesFromSheet($Sheet);               # worksheetデータ取得へ
        last;										# Sheet1で終了
    }

	$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 { # worksheetデータ取得
    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")
			{	# not defined in DB. register it.
				$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 (?, ?, ?, ?, ?, ?)";		# placeholders

				$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
			{	# patient exist. update item
				$cell = $Sheet->get_cell($i, 9);
				$itemName = $cell->value();
				$cell = $Sheet->get_cell($i, 10);
				$itemValue = $cell->value();
				&updateDB($patID, $testDate, $itemName, $itemValue);
			}
			
     	}

    }
    
}