Re: problem with LOAD DATA INFILE for date format

There may be a better way, but in migrating from Filemaker to MySQL, I encountered similar problems. My solution was to pass the data through Excel and use that to convert the dates into the required format (CTRL-1, Custom, yyyy-mm-dd). Of course, you ned to watch out for those nasty ' "' ' that Excel puts around any cell containing a comma. So you might need to filter the output from excel in a text editor too but if its a one-off, its worth it. george ----- Original Message ----- From: "Hendra" <handhx@yahoo.com> To: <php-db@lists.php.net> Sent: Friday, January 25, 2002 5:09 AM Subject: [PHP-DB] problem with LOAD DATA INFILE for date format > Hi, > > I have a sample data like below in text file (policy.txt). > > "PolicyNo","DateOfBirth","PaidToDate" > "0003573607"," 9/25/1973"," 8/27/2001" > "0000708802","11/26/1959"," 5/25/1998" > "0002776507"," 3/19/1973","11/18/1999" > "0002776703"," 3/13/1969","11/18/1999" > > Policy table structure: > +---------------+-------------+ > | Field | Type | > +---------------+-------------+ > | PolicyNumber | char(10) | > | DateOfBirth | date | > | PaidToDate | date | > +---------------+-------------+ > > When I do: > > LOAD DATA INFILE "policy.txt" INTO TABLE Policy > FIELDS TERMINATED BY ',' ENCLOSED BY '"' > LINES TERMINATED BY '\n' IGNORE 1 LINES; > > All data in field DateOfBirth & PaidToDate become "0000-00-00" > I notice this is because mysql only accept date format in "yyyy-mm-dd". > However, in my text file, the date format is in "mm/dd/yyyy" format. > > Any idea? > > Thanks in advance. > Hendra > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net