Vernon,
I'm using the:
LOAD DATA LOCAL INFILE /home/filename.txt INTO TABLE list;
which works just fine. Only problem is the date column
which is formatted as such 4/11/1970 comes out as -
00-
00
The date field is setup as Field: s_date, Type: date,
Null, YES, Key: BLANK, Default: Null, Extra BLANK.
Any ideas on how to resolve this?
=I assume that you have RTFM to know the formatting options for representing dates in
MySQL.
=Given the incompatibility of formats you have two choices (1) change the date data's
format before the LOAD
DATA, or (2) change it (er) afterwards. The choice will depend upon the current format
of the data file, the
tools you have at your disposal, and your skill set. I'm practising 'tinkering' in SQL
so...
=In the second case:
a) redefine the CREATE TABLE so that s_date is a string column (call it something
else, eg temp_date);
b) perform the LOAD DATA and check;
c) ALTER TABLE to insert the desired s_date column and format;
d) run an UPDATE to copy the data out of temp_date, reformatting it, and storing it
into s_date;
e) check, then ALTER TABLE to remove temp_date;
f) if s_date is an indexed column, do NOT add the index when you add the column (will
slow down/complicate the
processing in (d)) - but don't forget to ALTER TABLE to add the index at this time.
=ok?
=dn
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
