Validating and Reformatting Data - MySQL - page 443

Suppose that a file named somedata.csv contains 12 data columns in comma-separated
values (CSV) format. From this file you want to extract only columns 2, 11, 5, and 9,
and use them to create database rows in a MySQL table that contains name , birth ,
height , and weight columns. You must make sure that the height and weight are positive
integers, and convert the birth dates from MM/DD/YY format to CCYY-MM-DD format. How
can you do this?
So... how can you do that, based on the techniques discussed in the previous chapter
and this one?
Much of the work can be done using the utility programs developed here. Convert the
file to tab-delimited format with cvt_file.pl (see Recipe 11.6 ), extract the columns in the
desired order with yank_col.pl (see Recipe 11.7 ), and rewrite the date column to ISO
format with cvt_date.pl (see Recipe 12.12 ):
% cvt_file.pl --iformat=csv somedata.csv \
| yank_col.pl --columns=2,11,5,9 \
| cvt_date.pl --columns=2 --iformat=us --add-century > tmp
The resulting file, tmp , has four columns representing the name , birth , height , and
weight values, in that order. It needs only to have its height and weight columns checked
to make sure they contain positive integers. Using the is_positive_integer() library
function from the Cookbook_Utils.pm module file, that task can be achieved using a
short special-purpose script that is little more than an input loop:
#!/usr/bin/perl
# validate_htwt.pl: Height/weight validation example.
# Assumes tab-delimited, linefeed-terminated input lines.
# Input columns and the actions to perform on them are as follows:
# 1: name; echo as given
# 2: birth; echo as given
# 3: height; validate as positive integer
# 4: weight; validate as positive integer
use strict ;
use warnings ;
use Cookbook_Utils ;
while ( <> )
{
chomp ;
my ( $name , $birth , $height , $weight ) = split ( /\t/ , $_ , 4 );
warn "line $.:height $height is not a positive integer\n"
if ! is_positive_integer ( $height );
warn "line $.:weight $weight is not a positive integer\n"
if ! is_positive_integer ( $weight );
}