Database Reference
In-Depth Information
}
}
print
join
(
"\t"
,
@val
)
.
"\n"
;
}
The script only does reformatting, it doesn't validate the dates. To do that, modify the
script to use the
Cookbook_Utils.pm
module by adding this statement after the
use
warnings
line:
use
Cookbook_Utils
;
That gives the script access to the module's
is_valid_date()
routine. To use it, change
this line:
if
(
exists
(
$map
{
$month
}))
To this:
if
(
exists
(
$map
{
$month
})
&&
is_valid_date
(
$year
,
$map
{
$month
},
$day
))
12.13. Importing Non-ISO Date Values
Problem
Date values to be imported are not in the ISO (
CCYY-MM-DD
) format that MySQL expects.
Solution
Use an external utility to convert the dates to ISO format before importing the data into
MySQL (
cvt_date.pl
is useful here). Or use
LOAD
DATA
's capability for preprocessing input
data prior to loading it into the database.
Discussion
Suppose that a table contains three columns,
name
,
date
, and
value
, where
date
is a
DATE
column requiring values in ISO format (
CCYY-MM-DD
). Suppose also that you're
given a datafile
newdata.txt
to be imported into the table, but its contents look like this:
name1 01/01/99 38
name2 12/31/00 40
name3 02/28/13 42
name4 01/02/18 44
The dates are in
MM/DD/YY
format and must be converted to ISO format to be stored as
DATE
values in MySQL. One way to do this is to run the file through the
cvt_date.pl
script
from
Recipe 12.12
:
%
cvt_date.pl --iformat=us --add-century newdata.txt > tmp.txt
