Re: 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, > 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. > > 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.