using Load data in mysql

Question

I am trying to load some data from a csv file to mysql. This is on a raspberry pi.

I tried with "--local-infile=1" and without.

pi data > cat test.csv 
2014-10-30 08-09-08,1
2014-10-30 08-09-13,2
2014-10-30 08-09-18,3
2014-10-30 08-09-23,4
2014-10-30 08-09-28,5
2014-10-30 08-09-33,6
2014-10-30 08-09-38,7
2014-10-30 08-09-43,8
2014-10-30 08-09-48,9
2014-10-30 08-09-53,10

and this is what I tried:

pi data > mysql --uroot -ppasswd -s solar --local-infile=1

mysql> create table if not exists temp (
     -> time TIMESTAMP,
     -> voltage SMALLINT UNSIGNED,
     -> primary key (time)
     -> );

mysql> LOAD DATA INFILE 'test.csv' INTO TABLE 'temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY '\r\n' (time,voltage);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY '\r\n' (time,voltage)' at line 1
mysql> LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE 'temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY '\r\n' (time,voltage);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''temp' FIELDS  TERMINATED BY ','  LINES TERMINATED BY '\r\n' (time,voltage)' at line 1

Any help would be appreciated.

Thanks.

Robbert · Accepted Answer · 2014-10-30 21:51:33Z

2

You are using the wrong characters around your table name. MySql uses backticks not quotes for field and table names.

LOAD DATA INFILE 'test.csv' INTO TABLE `temp` FIELDS  TERMINATED BY ','  LINES TERMINATED BY '\r\n' (time,voltage);

answered Oct 30, 2014 at 21:51

Robbert

6,6226 gold badges37 silver badges64 bronze badges

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

4 Comments

user3259040 Over a year ago

Thanks. I was trying something slightly different and noticed that just the first line gets loaded. I am trying now to add two columns to voltage. load data infile '/home/pi/bin/test.csv' into table temp fields terminated by ',' columns terminated by ',' LINES TERMINATED BY '\r\n' (time,@v1,@v1) set voltage = @v1+@v2; This works but only the first line gets loaded. Why?

Robbert Over a year ago

Are you sure your lines are terminated by \r\n and not just \n

user3259040 Over a year ago

I tried both. mysql> load data infile '/home/pi/bin/test.csv' into table temp fields terminated by ',' LINES TERMINATED BY '\n' (time,@v1,@v1) set voltage = @v1+@v2; ERROR 1062 (23000): Duplicate entry '2014-10-30 08:09:08' for key 'PRIMARY' mysql> select * from temp; time voltage 2014-10-30 08:09:08 NULL mysql> load data infile '/home/pi/bin/test.csv' into table temp fields terminated by ',' LINES TERMINATED BY '\r' (time,@v1,@v1) set voltage = @v1+@v2; ERROR 1062 (23000): Duplicate entry '2014-10-30 08:09:08' for key 'PRIMARY' mysql> select * from temp; time volt 2014-10-30 08:09:08 NULL

Robbert Over a year ago

The error clearly states what the problem is here. You are creating a duplicate entry on your primary key, meaning there's already an entry in the database with the value 2014-10-30 08:09:08.

Collectives™ on Stack Overflow

using Load data in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related