Cannot load csv file into mysql table

Question

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?

Seidr · Accepted Answer · 2013-06-12 13:17:23Z

1

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

edited Jun 12, 2013 at 13:17

answered Jun 12, 2013 at 13:05

Seidr

4,9363 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

brucezepplin Over a year ago

I think that solves my intial querey, however it's resulting in ERROR 13 (HY000): Can't get stat - which i think is a permissions issue

Seidr Over a year ago

Yes, that sounds like a permission issue. Ensure the MySQL process has read permissions on the file in question (either by using CHMOD 777 (YUCK!) or chown to the user the mysql process runs as (better!))

Seidr Over a year ago

If you have access to a command line on the machine on which MySQL is running, you may be better off using mysqlimport. Updating my answer now.

brucezepplin Over a year ago

hmm only seems to add in one row of data using mysqlimport. also had to change tprot to my database name (which is different name). interesting. but you have solved my original querey so have accepted your answer. thanks.

Seidr Over a year ago

That's odd RE mysqlimport. Perhaps the lines are terminated by just a \n, not a \r\n combo.

Collectives™ on Stack Overflow

Cannot load csv file into mysql table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related