Database Reference
In-Depth Information
into MySQL, you must convert the dates to ISO format, resulting in a file that looks like
this:
Delaware 1787-12-07
Pennsylvania 1787-12-12
New Jersey 1787-12-18
Georgia 1788-01-02
Connecticut 1788-01-09
Massachusetts 1788-02-06
…
That's a somewhat specialized kind of transformation, although this general type of
problem (converting a particular date format to ISO format) is hardly uncommon. To
perform the conversion, identify the dates as those values matching an appropriate
pattern, map month names to the corresponding numeric values, and reformat the
result. The following script,
monddccyy_to_iso.pl
, illustrates how:
#!/usr/bin/perl
# monddccyy_to_iso.pl: Convert dates from mon[.] dd, ccyy to ISO format.
# Assumes tab-delimited, linefeed-terminated input
use
strict
;
use
warnings
;
my
%map
=
# map 3-char month abbreviations to numeric month
(
"jan"
=>
1
,
"feb"
=>
2
,
"mar"
=>
3
,
"apr"
=>
4
,
"may"
=>
5
,
"jun"
=>
6
,
"jul"
=>
7
,
"aug"
=>
8
,
"sep"
=>
9
,
"oct"
=>
10
,
"nov"
=>
11
,
"dec"
=>
12
);
while
(
<>
)
{
chomp
;
my
@val
=
split
(
/\t/
,
$_
,
10000
);
# split, preserving all fields
for
my
$i
(
0
..
@val
-
1
)
{
# reformat the value if it matches the pattern, otherwise assume
# that it's not a date in the required format and leave it alone
if
(
$val
[
$i
]
=~
/^([^.]+)\.? (\d+), (\d+)$/
)
{
# use lowercase month name
my
(
$month
,
$day
,
$year
)
=
(
lc
(
$1
),
$2
,
$3
);
if
(
exists
(
$map
{
$month
}))
{
$val
[
$i
]
=
sprintf
(
"%04d-%02d-%02d"
,
$year
,
$map
{
$month
},
$day
);
}
else
{
# warn, but don't reformat
warn
"$val[$i]: bad date?\n"
;
}
