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" <[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"
> "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: [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]

Reply via email to