Script for Importing Casualty Data

A few readers may know that the Pentagon publishes US military casualty data on a web page, and that every death from Operation Iraqi Freedom and Operation Enduring Freedom is cataloged and available for download on an Excel spreadsheet. (It's debatable whether every death is cataloged, but speculation is not the purpose of this post.) I have been attempting to mine data on this Excel sheet for some time, but it's not nearly as convenient as having it in a real database.

So after some thought and a little tinkering, I've developed a rough solution that will automatically download the latest spreadsheet, do some conversion, and import the data into a MySQL database. It's accomplished through a Linux shell script and a Perl script, so if you're on a Windows machine, you'll have to search for a different solution.


This is the shell script, I call it oifdata.sh:
#!/bin/sh<br/>filename=casdata<br/>printf "Grabbing xls from DoD..."<br/>wget -qNr http://siadapp.dior.whs.mil/personnel/CASUALTY/oif_list_of_names.xls -O /home/chris/oifdata/$filename.xls<br/>printf " Done.\nFormating to tsv..."<br/>perl oif_xls2tsv.pl $filename.xls > $filename.tsv<br/>printf " Done. Importing to mysql...\n"<br/>mysqlimport --local --delete -u oif -poif oif casdata.tsv<br/>printf "Done.\n"<br/>

The Perl script reference here is a slight edit from xls2csv from the Spreadsheet-ParseExcel Perl package (which you will need to install). Instead of comma-separated values, it outputs tab-separated values. Also, it is specialized to start and end at the correct rows. I would like to generalize this in the future, but it's a lot of extra code at the moment.
# -----------------------------------------------------------------------------<br/># oif_xls2tsv.pl<br/>#------------------------------------------------------------------------------<br/>### oif_xls2tsv<br/># based on xls2csv (for Spreadsheet::ParseExcel::Utility)<br/># specialty function for parsing the excel spreadsheet from the DoD concerning<br/># casualties in Operation Iraqi Freedom, available from<br/># http://siadapp.dior.whs.mil/personnel/CASUALTY/castop.htm<br/># @args $filename, location of xls<br/>use strict ;<br/>use Spreadsheet::ParseExcel;<br/>use Spreadsheet::ParseExcel::Utility qw(sheetRef);<br/><br/>if(!(defined $ARGV[0])) {<br/> usage( "Bad Args") ;<br/> exit;<br/>}<br/><br/>my $filename = $ARGV[0] ;<br/><br/>if( ! -f $filename) {<br/> usage( "File $filename does not exist") ;<br/> exit ;<br/>}<br/><br/>printf xls2csv($filename) ;<br/><br/>#------------------------------------------------------------------------------<br/><br/>sub xls2csv {<br/> my ($filename) = @_ ;<br/> my $rotate = 0 ;<br/> my $sheet = 0 ;<br/> my $output = "" ; <br/><br/> # start the excel object now<br/> my $oExcel = new Spreadsheet::ParseExcel ;<br/> my $oBook = $oExcel->Parse( $filename) ;<br/> # open the sheet<br/> my $oWkS = $oBook->{Worksheet}[ $sheet] ;<br/><br/> # define start and end of data<br/> my $s = "A4" ;<br/> my $e = "V" . ($oWkS->{MaxRow} - 6);<br/> my @start = sheetRef($s) ;<br/> my @end = sheetRef($e) ;<br/> if( !@start) {<br/> print STDERR "Bad start coorinates - $s";<br/> return "" ;<br/> }<br/> if( !@end) {<br/> print STDERR "Bad end coorinates - $e";<br/> return "" ;<br/> }<br/> <br/> if( $start[1] > $end[1]) {<br/> print STDERR "Bad COLUMN ordering\n";<br/> print STDERR "Start column " . int2col($start[1]);<br/> print STDERR " after end column " . int2col($end[1]) . "\n";<br/> return "" ;<br/> }<br/> if( $start[0] > $end[0]) {<br/> print STDERR "Bad ROW ordering\n";<br/> print STDERR "Start row " . ($start[0] + 1);<br/> print STDERR " after end row " . ($end[0] + 1) . "\n";<br/> exit ;<br/> }<br/> <br/> # now check that the region exists in the file<br/> # if not trucate to the possible region<br/> # output a warning msg<br/> if( $start[1] < $oWkS->{MinCol}) {<br/> print STDERR int2col( $start[1]) . " < min col " . int2col( $oWkS->{MinCol}) . " Reseting\n";<br/> $start[1] = $oWkS->{MinCol} ;<br/> }<br/> if( $end[1] > $oWkS->{MaxCol}) {<br/> print STDERR int2col( $end[1]) . " > max col " . int2col( $oWkS->{MaxCol}) . " Reseting\n" ;<br/> $end[1] = $oWkS->{MaxCol} ;<br/> }<br/> if( $start[0] < $oWkS->{MinRow}) {<br/> print STDERR "" . ($start[0] + 1) . " < min row " . ($oWkS->{MinRow} + 1) . " Reseting\n";<br/> $start[0] = $oWkS->{MinCol} ;<br/> }<br/> if( $end[0] > $oWkS->{MaxRow}) {<br/> print STDERR "" . ($end[0] + 1) . " > max row " . ($oWkS->{MaxRow} + 1) . " Reseting\n";<br/> $end[0] = $oWkS->{MaxRow} ;<br/> <br/> }<br/><br/> my $x1 = $start[1] ;<br/> my $y1 = $start[0] ;<br/> my $x2 = $end[1] ;<br/> my $y2 = $end[0] ;<br/><br/> for( my $y = $y1 ; $y <= $y2 ; $y++) {<br/> for( my $x = $x1 ; $x <= $x2 ; $x++) {<br/> my $cell = $oWkS->{Cells}[$y][$x] ;<br/> $output .= $cell->Value if(defined $cell);<br/> $output .= "\t" if( $x != $x2) ;<br/> }<br/> $output .= "\n" ;<br/> }<br/> <br/> return $output ;<br/>}<br/># -----------------------------------------------------------------------------<br/>### error<br/># writes a message to STDERR<br/>#<br/>sub error {<br/> printf STDERR shift ;<br/>}<br/># -----------------------------------------------------------------------------<br/>### usage<br/># this decribes how the program as a whole is to be used<br/>#<br/>sub usage {<br/> my $errmsg = shift ;<br/><br/> error( "\n" . $errmsg . "\n") if( $errmsg) ;<br/><br/> error( "<br/>Usage: $0 filename \n\n") ;<br/>}<br/>

Lastly, my database table is set up like so. This was generated using the mysqldump script included with MySQL. I'm considering adding this file to the main script so that the auto-incrementing key will go back to zero each time the data is refreshed.
-- dbdump.txt<br/>-- Table structure for table `casdata`<br/>--<br/><br/>DROP TABLE IF EXISTS `casdata`;<br/>CREATE TABLE `casdata` (<br/> `Service` char(1) NOT NULL,<br/> `Component` char(1) NOT NULL,<br/> `Name` varchar(70) NOT NULL,<br/> `Rank` varchar(5) NOT NULL,<br/> `PayGrade` varchar(5) NOT NULL,<br/> `DateOfDeath` date NOT NULL,<br/> `IncidentDate` date NOT NULL,<br/> `Hostile` char(1) default NULL,<br/> `Age` int(10) unsigned NOT NULL,<br/> `Gender` char(1) NOT NULL,<br/> `HomeOfRecordCity` varchar(45) NOT NULL,<br/> `HomeOfRecordCounty` varchar(45) default NULL,<br/> `HomeOfRecordState` varchar(5) NOT NULL,<br/> `HomeOfRecordCountry` varchar(5) NOT NULL,<br/> `Unit` varchar(100) NOT NULL,<br/> `IncidentGeographicCode` varchar(2) default NULL,<br/> `CasualtyGeographicCode` varchar(2) default NULL,<br/> `CasualtyCountry` varchar(20) default NULL,<br/> `CityOfLoss` varchar(45) default NULL,<br/> `RaceEthnic` varchar(20) default NULL,<br/> `OccupationCode` varchar(5) default NULL,<br/> `OccupationCodePrefix` varchar(5) default NULL,<br/> `ID` int(10) unsigned NOT NULL auto_increment,<br/> PRIMARY KEY (`ID`)<br/>) ENGINE=InnoDB DEFAULT CHARSET=latin1;<br/><br/>

There you have it. Now you too can have a script to fill your database with the latest casualty information from the DoD. How you use that data is up to you...