Re: [PHP-DB] problem with LOAD DATA INFILE for date format
Hi Hendra An alternative would be to:- 1 LOAD DATA the table, but format each date field as a string. 2 add a date column 3 use UPDATE to convert/re-format the data from the string to date field 4 remove the string column This solution does not require you to tackle another tool and face the extra automated format changes; everything is within MySQL and can be visually scanned/carefully checked stage-by-stage. Regards, =dn > 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, > -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. > > I have a sample data like below in text file (policy.txt). > > > > "PolicyNo","DateOfBirth","PaidToDate" > > "0003573607"," 9/25/1973"," 8/27/2001" > > "708802","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 "-00-00" > > I notice this is because mysql only accept date format in "-mm-dd". > > However, in my text file, the date format is in "mm/dd/" format. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] 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, -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" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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" > "708802","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 "-00-00" > I notice this is because mysql only accept date format in "-mm-dd". > However, in my text file, the date format is in "mm/dd/" format. > > Any idea? > > Thanks in advance. > Hendra > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
