CODEWITHSUNDEEP
mysqlcsvpythonanywhere
elksie5000
elksie5000

Reputation: 7752

LOAD DATA LOCAL INFILE command imports no data

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.

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.

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

UPDATE: Here's the top of the 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

Upvotes: 1

Views: 2089

Answers (1)

elksie5000
elksie5000

Reputation: 7752

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

Upvotes: 1

Related Questions