LOAD DATA LOCAL INFILE命令不导入任何数据

I've been trying to upload a csv file into a table on a mysql database on pythonanywhere and I can't work out why the LOAD DATA LOCAL INFILE command isn't working.

我一直在尝试将一个csv文件上传到pythonanywhere上mysql数据库的表中,我无法理解为什么LOAD DATA LOCAL INFILE命令不起作用。

This was my command and result:

这是我的命令和结果:

mysql> LOAD DATA LOCAL INFILE '/twenty_year_change_data.csv' 
    -> INTO TABLE twenty_year_change_data 
    -> FIELDS TERMINATED BY ',' 
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

I tried the command again, but took off the bit to remove the header:

我再次尝试了这个命令,但取消了删除标题:

mysql> LOAD DATA LOCAL INFILE '/twenty_year_change_data.csv' 
    -> INTO TABLE twenty_year_change_data 
    -> FIELDS TERMINATED BY ',' 
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2

Looking at the warning:

看警告:

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'pcode_district' at row 1 |
| Warning | 1265 | Data truncated for column 'percent_change' at row 1 |
+---------+------+-----------------------------------------------------+
2 rows in set (0.00 sec)

Here's what the table looks like:

这是表格的样子:

mysql> select * from twenty_year_change_data;
+----------------+------+------+----------------+
| pcode_district | 1995 | 2015 | percent_change |
+----------------+------+------+----------------+
| pcode_         | 1995 | 2015 |              0 |
+----------------+------+------+----------------+
1 row in set (0.00 sec)

Looking at the format of the data:

看看数据的格式:

mysql> describe twenty_year_change_data
    -> ;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| pcode_district | varchar(6) | NO   |     | NULL    |       |
| 1995           | float      | YES  |     | NULL    |       |
| 2015           | float      | YES  |     | NULL    |       |
| percent_change | float      | YES  |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

I guessed it might be a problem with the underscore so I tweaked the csv file so the headers don't contain them, but it still stops after importing just the first cell.

我猜测它可能是下划线的一个问题所以我调整了csv文件,所以标题不包含它们,但它仍然在导入第一个单元格后停止。

How do I fix the problem, and what do I need to be aware about formats when working with mysql?

我如何解决这个问题,在使用mysql时我需要注意哪些格式?

UPDATE: Here's the top of the csv

更新:这是csv的顶部

pcode_district,1995,2015,percent_change
AL1 1,79700,427500,436.3864492
AL1 2,78125,384250,391.84
AL1 3,66500,306500,360.9022556
AL1 4,98000,575000,486.7346939
AL1 5,72000,365250,407.2916667

1 个解决方案

#1

Thank you to @barmar for the answer. The problem was that the csv file lines were terminated by '\r\, rather than '\n'.

感谢@barmar的回答。问题是csv文件行被'\ r \ \而不是'\ n'终止。