MySQL CSV 文件导入日期时间

Question

新来的 - 要友好。

我正在将一个 CSV 文件导入到一个 mysql 表中，并且在使用 datetime 类型时遇到了特别困难的时间。

我的导入示例，日期时间值将是中的第 4 个字段。

3026930,{5849EF5A-210E-497D-948D-0006687AD627},550000,28/01/2013          00:00,1,1,2013,2013/1,SE145DS,S,N,F,3, ,NEW CROSS ROAD, ,LONDON,LEWISHAM,GREATER LONDON,     ,SE145DS,Inner,E09000023,Lewisham,E05000449,New Cross,E02000655,E01003302,E00016694 

我的导入语句:

    LOAD DATA INFILE '/tmp/house1.csv'
          INTO TABLE housesales
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
              IGNORE 1 ROWS
                 SET dateprocessed = STR_TO_DATE(@dateprocessed, '%d/%m/%Y %H:%i');

我的 table :

+----------------+------------------+------+-----+---------+-------+
| Field          | Type             | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| saleid         | int(10) unsigned | NO   | PRI | NULL    |       |
| transactionID  | char(50)         | NO   |     | NULL    |       |
| price          | int(10) unsigned | NO   |     | NULL    |       |
| dateprocessed  | datetime         | NO   |     | NULL    |       |
| quarter        | tinyint(4)       | NO   |     | NULL    |       |
| month          | tinyint(4)       | NO   |     | NULL    |       |
| year           | smallint(6)      | NO   |     | NULL    |       |
| yearmonth      | char(10)         | NO   |     | NULL    |       |
| postcode       | char(10)         | NO   |     | NULL    |       |
| propertytype   | char(3)          | NO   |     | NULL    |       |
| whethernew     | char(3)          | NO   |     | NULL    |       |
| tenure         | char(3)          | NO   |     | NULL    |       |
| address1       | char(50)         | YES  |     | NULL    |       |
| address2       | char(50)         | YES  |     | NULL    |       |
| address3       | char(50)         | YES  |     | NULL    |       |
| address4       | char(50)         | YES  |     | NULL    |       |
| town           | char(20)         | YES  |     | NULL    |       |
| localauthority | char(30)         | YES  |     | NULL    |       |
| county         | char(30)         | YES  |     | NULL    |       |
| recordstatus   | char(2)          | YES  |     | NULL    |       |
| postcodeclean  | char(10)         | YES  |     | NULL    |       |
| innerouter     | char(10)         | YES  |     | NULL    |       |
| boroughcode    | char(20)         | YES  |     | NULL    |       |
| boroughname    | char(30)         | YES  |     | NULL    |       |
| wardcode       | char(20)         | YES  |     | NULL    |       |
| wardname       | char(30)         | YES  |     | NULL    |       |
| msoa11         | char(20)         | YES  |     | NULL    |       |
| lsoa11         | char(20)         | YES  |     | NULL    |       |
| oa11           | char(20)         | YES  |     | NULL    |       |
+----------------+------------------+------+-----+---------+-------+

最后是导入表中的一行:

| 3118826 | {F3F11295-2C5B-44E5-870A-ED719FFB12C9} | 100500 | 0000-00-00 00:00:00 |2 |     5 | 2013 | 2013/5    | E16 1BY  | F            | N          | L      | OCEANIS APARTMENTS, 19     | FLAT 38               | SEAGULL LANE            |                      | LONDON               | NEWHAM                 | GREATER LONDON |              | E16 1BY       | Inner      | E09000025   | Newham               | | E05000478 | Canning Town South             | E02000747 | E01033576 | E00174967

我也收到了超出范围的警告，如果日期时间列变得像空值一样，这是预期的。会不会是空格过多？

谢谢迈克尔

Answer 1

您的 csv 文件是否有标题？如果它有一个，只需将其删除并重试。

我尝试了在另一篇文章中看到的这个命令，它运行良好:

 LOAD DATA LOCAL INFILE ' test.csv'
INTO TABLE  housesales
FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY ','
LINES TERMINATED BY '\n'
(row1,row2,row3,etc...)