Search code examples
mysqlimportload-data-infile

mysql load data local infile

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!

Solution

  • 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.