Mysql – thesql load data local infile

importload-data-infileMySQL

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!

Best Answer

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.

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Mysql – How to import CSV file into a MySQL table

Use mysqlimport to load a table into the database:

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --local -u root \
            -p Database \
             TableName.csv

I found it at http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/

To make the delimiter a tab, use --fields-terminated-by='\t'

Best Answer

Related Solutions

Mysql – Should I use the datetime or timestamp data type in MySQL

Mysql – How to import CSV file into a MySQL table

Related Topic