A "smart" (forgiving) date parser?

Question

I have to migrate a very large dataset from one system to another. One of the "source" column contains a date but is really a string with no constraint, while the destination system mandates a date in the format yyyy-mm-dd.

Many, but not all, of the source dates are formatted as yyyymmdd. So to coerce them to the expected format, I do (in Perl):

return "$1-$2-$3" if ($val =~ /(\d{4})[-\/]*(\d{2})[-\/]*(\d{2})/);

The problem arises when the source dates moves away from the "generic" yyyymmdd. The goal is to salvage as many dates as possible, before giving up. Example source strings include:

21/3/1998, March 2004, 2001, 3/4/97

I can try to match as many of the examples I can find with a succession of regular expressions such as the one above.

But is there something smarter to do? Am I not reinventing the wheel? Is there a library somewhere doing something similar? I couldn't find anything relevant googling "forgiving date parser". (any language is OK).

Answer 1

Are you looking for the Date::Parse module?

Answer 2

Date::Manip is your friend, as is fails on only one out of four because it assumes US format, using Date_Init you can get 4 out of 4.

If you have different formats (ie, month before day and viceversa) you'd have to parse them differently, once with US date format and the next with a non-US date format. This is especially important when it's ambiguous, like your 3/4/97 example, because if it's 21/3 it just fails and you can tell the format is wrong.

vinko@mithril:~$ more date.pl
use strict;
use warnings;
use Date::Manip;

my @a;
push @a, "March 2004";
push @a, "2001";
push @a, "3/4/97";
push @a, "21/3/1998";
Date_Init("DateFormat=non-US");
for my $d (@a) {
    print "$d\n";
    print ParseDate($d)."\n";
};
vinko@mithril:~$ perl date.pl
March 2004
2004030100:00:00
2001
2001010100:00:00
3/4/97
1997040300:00:00
21/3/1998
1998032100:00:00

Answer 3

You might also take a look at DateTime::Format::Flexible

Based on its description, it's right up your alley:

If you have ever had to use a program that made you type in the date a certain way and thought "Why can't the computer just figure out what date I wanted?", this module is for you.

DateTime::Format::Flexible attempts to take any string you give it and parse it into a DateTime object.

I ran a version of Vinko's script using this module just now, and got similar results. Everything is fine except for the last case (21/3/1998). As with Date::Manip, you can handle this relatively easily by explicitly setting a parameter (european => 1). Danbystrom's comment shows why such cases need human oversight.

Answer 4

It's not perl but, this .NET library will parse a wide range of date/time strings.

Answer 5

I finally extracted a test set of more than 200 examples of dates that actually occur in the data set. Some are mildly misbehaved, a few are totally sick ("01010" for example).

I tried all the existing Perl modules I could find, but the success rate was too low. I eventually dived in an reinvented my wheel, achieving a more than 98% success rate.

My algorithm is a succession of increasingly fuzzier recognizers, starting with the rigidly valid dates downs to total guess territory. The first to return a "success" result wins. In the middle of that stack, I have the "main" recognizer which does something like this:

• parse sets of numbers in the string, anywhere. "months names" in French and in English are recognized also.

• For each of them I put them in three buckets: candidates for year, candidates for month, candidates for day. For example "13" will be in the "possible year" bucket, and in the "possible day" bucket. "February" will only go in the "months" bucket of course. In each bucket, the value is tagged with a "plausibility level", an arbitrary number that depends on a number of things. For example, 2010 is more plausible as a year than 10.

• look in each of the three buckets. If any of them has only one item in it, it's the value for that bucket. It's also removed from the other buckets.

• look for the remaining missing values in their respective buckets in order (year, month, day), taking the one with the highest plausibility. In case of tie, take the one occurring last in the string (actually, those have slightly higher plausibility). This rules breaks 7/3/2010 as march 7, as I need here in France. Remove that value from the other buckets, should the case apply.

• if any value is missing, use a default value (eg I use 8191 as the default year, the largest allowed value in my target system).

The whole thing is awfully heuristics, but fits my requirement that it's better to have garbage than to lose information.