MySQL CSV import：datetime value [英] MySQL CSV import: datetime value

问题描述

这是导入语句。


LOAD DATA LOCAL INFILE'myData.csv'
INTO TABLE equity_last_import
FIELDS TERMINATED BY'，'
LINES TERMINATED BY'\\\
'
（股票，最后，@ last_date）
SET last_date = STR_TO_DATE（@last_date，'％Y-％m- ％d％H：％i：％s'）


以下是数据示例：

 
 4108,48.74，2013-09-16 16:15:04
 4249,8.1，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
 

我可以使用 STR_TO_DATE （），但我无法获取datetime值导入。我尝试了几种不同的日期格式，除了'％Y-％m-％d％H：％i：％s'，我总是得到一个空日期时间[0000-00-00 00:00:00]。我也尝试不使用 STR_TO_DATE（），因为字符串是默认的MySQL datetime格式。

任何帮助将不胜感激。

解决方案

数据文件已经是MySQL应该在本机理解的格式。它只是用双引号括起来。你需要告诉 LOAD DATA INFILE 如何处理报价。尝试这样：

 负载数据本地INFILE'myData.csv'
 INTO TABLE equity_last_import 
 FIELDS可选择由'''''''''''''''''''''''''''''''''''$'
 p> 
 
 
更新：
 
 
 
因为你说它不工作，我创建了一个测试表，下面是证明：
 
 
 
我从问题中突出显示了您的csv数据，并粘贴到一个名为 myData.csv <然后我连接到mysql控制台，切换到 test 数据库并运行以下命令：
  mysql> create table equity_last_import（equity int，last decimal（10,2），last_date datetime） engine = innodb; 
查询OK，0行受影响（0.02秒）
 
 mysql> LOAD DATA LOCAL INFILE'/tmp/myData.csv'
  - > INTO TABLE equity_last_import 
  - >可选择地由'TERMINATED BY'，'
  - > LINES TERMINATED BY'\\\
'
  - > （equity，last，last_date）; 
查询OK，10行受影响（0.00秒）
记录：10已删除：0已跳过：0警告：0 
 
 mysql>选择* from equity_last_import; 
 + -------- + -------- + --------------------- + 
 |股权|最后| 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 set in set（0.00 sec）
  
 
 
 
 
另一个更新：
 
 
 
您已指定您现在收到以下错误：
 超出列1的列last_date的范围
  pre> 
 
 
您的CSV文件有标题吗？如果是这样，您可能希望在 LOAD DATA INFILE 命令中添加 IGNORE 1 LINES ，以告诉MySQL跳过
 
I have been banging my head against a wall trying to import datetime values from a .csv file.

Here's the import statement.


LOAD DATA LOCAL INFILE 'myData.csv' 
                INTO TABLE equity_last_import
                FIELDS TERMINATED BY ','
                LINES TERMINATED BY '\n'
                (equity,last,@last_date) 
                SET last_date = STR_TO_DATE( @last_date, '%Y-%m-%d %H:%i:%s')


Here's a sample of the data:
4108,48.74,"2013-09-16 16:15:04"
4249,8.1,"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"
I am able to import date values using STR_TO_DATE() but I not able to get datetime values to import.  I have tried several different date formats other than '%Y-%m-%d %H:%i:%s' and I always get a null datetime [0000-00-00 00:00:00].  I have also tried not using STR_TO_DATE(), since the string is in the default MySQL datetime format. 

Any help will be appreciated.
解决方案
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.

                        
这篇关于MySQL CSV import：datetime value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！