MySQL CSV import: datetime value

The date in your data file is already in a format MySQL should natively understand. It's just enclosed in double quotes. You need to tell LOAD DATA INFILE how to deal with the quotes. Try something like this:

LOAD DATA LOCAL INFILE 'myData.csv'
INTO TABLE equity_last_import
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
(equity,last,last_date)

Update:

Since you've said it doesn't work, I created a test table and verified that it does work. Here's the proof:

I've highlighted your csv data from the question and pasted into a new file called myData.csv in my system's /tmp folder. Then I connected to the mysql console, switched to the test database and ran the following:

mysql> create table equity_last_import (equity int, last decimal(10,2), last_date datetime) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA LOCAL INFILE '/tmp/myData.csv'
    -> INTO TABLE equity_last_import
    -> FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (equity,last,last_date);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from equity_last_import;
+--------+--------+---------------------+
| equity | last   | last_date           |
+--------+--------+---------------------+
|   4108 |  48.74 | 2013-09-16 16:15:04 |
|   4249 |   8.10 | 2013-09-16 16:15:04 |
|   4197 |   3.81 | 2013-09-16 17:20:00 |
|   4139 |  26.81 | 2013-09-16 16:15:04 |
|   4218 |  24.83 | 2013-09-16 17:20:00 |
|   4260 |  79.72 | 2013-09-16 16:15:04 |
|   4270 | 450.12 | 2013-09-16 17:20:00 |
|   4242 |  30.38 | 2013-09-16 16:15:04 |
|   4193 |   1.42 | 2013-09-16 16:15:04 |
|   4134 |   3.77 | 2013-09-16 16:15:04 |
+--------+--------+---------------------+
10 rows in set (0.00 sec)

See? It works perfectly.

Another Update:

You've specified that you're getting the following error now:

Out of range value for column 'last_date' at row 1

Does your CSV file have a header? If so, you may want to add IGNORE 1 LINES to your LOAD DATA INFILE command to tell MySQL to skip over the header.


I ran into the same problem. I fixed it by changing the format for the date column in my CSV file to match the MySQL datetime format.

  1. Open CSV in Excel.
  2. Highlight the column.
  3. Right-click on the column.
  4. Click on Format Cells.
  5. Pick Custom.
  6. Use yyyy/mm/dd hh:mm:ss in the Type field.
  7. Click ok

My CSV successfully imported after I changed the datetime format as above.

Tags:

Mysql

Csv

Recent Posts