mysql load data local infile

Question

I'm trying to load data into a mysql table using LOAD DATA LOCAL INFILE using the code below.

Mysql:

LOAD DATA INFILE '/var/www/vhosts/domain.com/httpdocs/test1.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (recloc,client_acc)

Edit: changed LOAD DATA LOCAL INFILE to LOADA DATA INFILE, removed SET id=null, added IGNORE 1 LINES

I'm getting no errors and no imported records. I believe the issue is related to the column names but i'm having a hard time fully understanding what those names should be. Should they be the actual column names within the CSV? or the field names in the DB Table? I would also like the have an auto_incremented primary key (id).

CSV:

recloc,client_acc
"NLGSX3","CORPORATE"
"7SC3BA","QUALITY ASSURANCE"
"3B9OHF","90717-6710"

Any suggestions to what I may be doing wrong? thanks!

score 2 · Accepted Answer · answered Dec 14 '10 at 16:51

2

Column names in CSV are not necessary, so you should add IGNORE 1 LINES clause.

Columns in your query (recloc,client_acc) need to match columns in table. First column from CSV will be inserted into recloc, second into client_acc.

If you don't specifu AUTO_INCREMENT column in the statement, but there is one in the table, it should fill automatically.

answered Dec 14 '10 at 16:51

Mchl

61,444
9
118
120

"IGNORE 1 LINES" added, removed "SET id=null", Still no luck. I'm also storing the CSV in the directory of the import script but i've also tried the server path. Shouldn't I be getting some kind of mysql error? – Stephen S. Dec 14 '10 at 17:31
After more research i changed LOAD DATA LOCAL INFILE to LOAD DATA INFILE since i'm using files stored on the server. – Stephen S. Dec 14 '10 at 18:18

score 0 · Answer 2 · edited Jan 14 '15 at 14:01

Short and sweet solution for excel to mysql data import:

Working good for txt file formats. IN DETAIL:

tbl name=t1
feilds are= name varchar,email varchar;

text.txt file <<== this text file first lines table column names:

name,   email
"n1",   "e1"  next line 
"n2",   "e2" next line 
"n3",   "e3" next line 
"n4",   "e4" next line 
"n5",   "e5" next line 
"n6",   "e6" next line 
"n7",   "e7" next line

pls ignore next line statements SQL query in wamp

LOAD DATA INFILE 'c:/wamp/www/touch/text.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES(name,email)

For this commnad run successfully we have create folders for separately.

Real one is

C:\wamp\mysql\data\wamp\www\touch\text.txt <<==pysical file path is.

But we mention c:/wamp/touch/text.txt

mysql load data local infile

2 Answers2

Linked