asked on

How can I convert m/d/yyyy to YYYY-MM-DD in a CSV file?

Hi EEs,
I'm working on converting and cleansing a database dump from an older FileMaker Pro database to import into MySQL (for a web project) and need a nice tidy way to convert the date format of m/d/yyyy to YYYY-MM-DD. I know perl, sed and awk should be able to do the trick, but what I've found so far hasn't worked (definitely a syntax problem). It'd be great if your solution was a perl script that could be run as "date-fmt.pl FILENAME", but even a simple sed command would be appreciated. I'm dealing with a database that has over 230,000 entries and using OpenOffice extensions to change the format takes wayyyyy too long.

digitaly

ASKER

While I'm at it, if you can add the process in this sed command to the script, too, that'd be excellent. No sweat if you can't or don't though.

sed -e 's/\"//g' -e 's/\\//g' -e 's/^/\"/' -e 's/$/\"/' -e 's/\t/\"\,\"/g'

sjklein42

perl script

while ( <> )
{
	# m/d/yyyy
	# YYYY-MM-DD
	while (/([0-9]+)\/([0-9]+)\/([0-9]+)/)
	{
	    $_ = $` . $3 . '-' . sprintf("%02d",$1) . '-' . sprintf("%02d",$2) . $';
	}

	print $_;
}

Open in new window

ASKER CERTIFIED SOLUTION

sjklein42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

digitaly

ASKER

Awesome! Worked great! Thanks a load!!! :-)

digitaly

ASKER

Excellent, accurate and FAST response. THANK YOU!!!

sjklein42

thanks