CODEWITHSUNDEEP
mysqlload
brucezepplin
brucezepplin

Reputation: 9752

Cannot load csv file into mysql table

I am trying to load a csv file into a table using:

LOAD DATA INFILE 'tprot.csv' INTO TABLE tprot FIELDS TERMINATED BY ',' LINES TERMINATED BY `'\n' SET id=null;`

and getting the following:

ERROR 1054 (42S22): Unknown column 'id' in 'field list'

Here are the columns in my table:

mysql> show columns from tprot;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| idindex   | int(10)     | YES  |     | NULL    |       |
| correct   | varchar(10) | YES  |     | NULL    |       |
| incorrect | varchar(10) | YES  |     | NULL    |       |
| 1a        | varchar(20) | YES  |     | NULL    |       |
| 2a        | varchar(20) | YES  |     | NULL    |       |
| 3a        | varchar(20) | YES  |     | NULL    |       |
| 4a        | varchar(20) | YES  |     | NULL    |       |
| 5a        | varchar(20) | YES  |     | NULL    |       |
| 6a        | varchar(20) | YES  |     | NULL    |       |
| 7a        | varchar(20) | YES  |     | NULL    |       |
| 8a        | varchar(20) | YES  |     | NULL    |       |
| 9a        | varchar(20) | YES  |     | NULL    |       |
| 10a       | varchar(20) | YES  |     | NULL    |       |
| 11a       | varchar(20) | YES  |     | NULL    |       |
| 12a       | varchar(20) | YES  |     | NULL    |       |
| 13a       | varchar(20) | YES  |     | NULL    |       |
| 14a       | varchar(20) | YES  |     | NULL    |       |
| 15a       | varchar(20) | YES  |     | NULL    |       |
| 16a       | varchar(20) | YES  |     | NULL    |       |
| 17a       | varchar(20) | YES  |     | NULL    |       |
| 18a       | varchar(20) | YES  |     | NULL    |       |
| 19a       | varchar(20) | YES  |     | NULL    |       |
| 20a       | varchar(20) | YES  |     | NULL    |       |
| 21a       | varchar(20) | YES  |     | NULL    |       |
| 22a       | varchar(20) | YES  |     | NULL    |       |
| 23a       | varchar(20) | YES  |     | NULL    |       |
| 24a       | varchar(20) | YES  |     | NULL    |       |
| 25a       | varchar(20) | YES  |     | NULL    |       |
| 26a       | varchar(20) | YES  |     | NULL    |       |
| 27a       | varchar(20) | YES  |     | NULL    |       |
| 28a       | varchar(20) | YES  |     | NULL    |       |
| 29a       | varchar(20) | YES  |     | NULL    |       |
| 30a       | varchar(20) | YES  |     | NULL    |       |
| 31a       | varchar(20) | YES  |     | NULL    |       |
| 32a       | varchar(20) | YES  |     | NULL    |       |
| 33a       | varchar(20) | YES  |     | NULL    |       |
| 34a       | varchar(20) | YES  |     | NULL    |       |
| 35a       | varchar(20) | YES  |     | NULL    |       |
| 36a       | varchar(20) | YES  |     | NULL    |       |
| 37a       | varchar(20) | YES  |     | NULL    |       |
| 38a       | varchar(20) | YES  |     | NULL    |       |
| 39a       | varchar(20) | YES  |     | NULL    |       |
| 40a       | varchar(20) | YES  |     | NULL    |       |
| 41a       | varchar(20) | YES  |     | NULL    |       |
| 42a       | varchar(20) | YES  |     | NULL    |       |
| 43a       | varchar(20) | YES  |     | NULL    |       |
| 44a       | varchar(20) | YES  |     | NULL    |       |
| 45a       | varchar(20) | YES  |     | NULL    |       |
| 46a       | varchar(20) | YES  |     | NULL    |       |
| 47a       | varchar(20) | YES  |     | NULL    |       |
| 48a       | varchar(20) | YES  |     | NULL    |       |
| 49a       | varchar(20) | YES  |     | NULL    |       |
| 50a       | varchar(20) | YES  |     | NULL    |       |
| 51a       | varchar(20) | YES  |     | NULL    |       |
| 52a       | varchar(20) | YES  |     | NULL    |       |
| 53a       | varchar(20) | YES  |     | NULL    |       |
| 54a       | varchar(20) | YES  |     | NULL    |       |
| 55a       | varchar(20) | YES  |     | NULL    |       |
| 56a       | varchar(20) | YES  |     | NULL    |       |
| 57a       | varchar(20) | YES  |     | NULL    |       |
| 58a       | varchar(20) | YES  |     | NULL    |       |
| 59a       | varchar(20) | YES  |     | NULL    |       |
| 60a       | varchar(20) | YES  |     | NULL    |       |
| 61a       | varchar(20) | YES  |     | NULL    |       |
| 62a       | varchar(20) | YES  |     | NULL    |       |
| 63a       | varchar(20) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
66 rows in set (0.00 sec)

and the link to the file is:

https://docs.google.com/file/d/0B0iDswLYaZ0zSVFUTlNUV1dMQkU/edit?usp=sharing

Why am I getting this error message?

Upvotes: 0

Views: 1369

Answers (1)

Seidr
Seidr

Reputation: 4936

You are telling SQL to set the column 'id' to null. There is no such column in your table.

Try this instead.

LOAD DATA INFILE 'tprot.csv' INTO TABLE tprot FIELDS TERMINATED BY ',' LINES TERMINATED BY `'\n' SET idindex=null;`

That said, this would probably result in null IDs for every row, instead of the imported value.

Completely un-tested, but this is my first assumption.

Edit:

Perhaps try using mysqlimport from the command line.

mysqlimport --local --fields-terminated-by=\, --lines-terminated-by=\r\n tprot tprot.csv

Reference: http://www.linuxcommand.org/man_pages/mysqlimport1.html

Upvotes: 1

Related Questions