DataMystic

First of all, I'd like to start out in praising the Textpipe Pro program. I have owned it for almost 3 years now and it has been worth every single penny for all that it is capable of!

I recently ran into a problem and honestly, I haven't used Textpipe for a few months so I have gotten a bit rusty.

I am trying to figure out how to convert a date format into the valid mySQL date format.
I have several text files with up to 200,000 lines (records) in each that I am trying to import into a mySQL database.
Here are some examples of the current format:

10/25/1999
2/26/2001
3/5/2002
11/5/2004
9/16/1998
10/23/99
11/5/01

As you can see, the format is MM/DD/YYYY (no leading zeros) and MM/DD/YY (also no leading zeros). There are no leading zeros in the singulars and it uses slashes instead of hyphens. If the date is a 4 or 2 digit year, at least it is usually consistent within each text file (a file will have either a 2 digit year or a 4 digit year but typically not both within the same file).

How do I convert this data into the valid Date type for mySQL as contained in the documentation:

A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL
displays DATE values in 'YYYY-MM-DD' format, but allows assignment of
values to DATE columns using either strings or numbers.

You need to use a series of date perl patterns:

To pickup 1 digit days
/(\d)/ -> /0$1/

To pickup 1 digit months
([^\d/])(\d)/ -> $1$0$2/

To pickup 2 digit years (can be more complicated)
/(0\d)[^\d/] -> /20$1$$2 for years with leading zero (assume 2000+)
/([^0]\d)[^\d/] -> /19$1$$2 for years without leading zero (assume 1900+)

Then convert this lot to the new format:
(/d{2})/(/d{2})/(/d{4}) -> $3-$2-$1