Well, if you look at the syntax for the LOAD DATA statement you'll notice the last two lines of it:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
This means you can specify the columns which the data is going into, in the order
from left to right of the columns in the file.
You shouldn't have to worry about the AUTO_INCREMENT field.
The date isn't in the correct format though. You need the CSV to look like
YYYY-MM-DD for the dates...
I added an extra column to keep the original date string...
mysql>
mysql> use test;
Database changed
mysql>
mysql> drop table if exists import;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create table import (
-> id mediumint auto_increment,
-> the_date date,
-> date_str varchar(50),
-> high double,
-> low double,
-> close double,
-> primary key (id));
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> describe import;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | mediumint(9) | NO | PRI | NULL | auto_increment |
| the_date | date | YES | | NULL | |
| date_str | varchar(50) | YES | | NULL | |
| high | double | YES | | NULL | |
| low | double | YES | | NULL | |
| close | double | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
mysql>
mysql> LOAD DATA INFILE 'J:/Temporary/Import-Test.txt' INTO TABLE import
-> fields terminated by ','
-> lines terminated by '\r\n'
-> (date_str, high, low, close);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql> select * from import;
+----+----------+------------+------+------+-------+
| id | the_date | date_str | high | low | close |
+----+----------+------------+------+------+-------+
| 1 | NULL | 12/29/2008 | 2.8 | 2.92 | 2.78 |
| 2 | NULL | 12/22/2008 | 2.93 | 2.99 | 2.65 |
| 3 | NULL | 12/15/2008 | 2.8 | 3.05 | 2.7 |
+----+----------+------------+------+------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> notee
Unless one of the SQL Wizards knows how to convert your date format into a DATE data.
>
Thanks, Jay