How to use MySQL LOAD DATA LOCAL INFILE

Posted on March 16, 2014 at 7:46 pm

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. It can be used to import a large bulk of data into MySQL and it is really very fast and uses less CPU compared with the default INSERT statements, you can read more information on the MySQL reference manual.

Load data into the column specified in the command:

Execute this query in the mysql prompt:

LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (username);

Alternatively execute this query with mysqli->query:

$mysqli->query( "LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (username)" );

This is the content inside the CSV file:

1,"Name Example"
2,"Name Example"
3,"Name Example"

Load data into the column specified in the CSV file:

Execute this query in the mysql prompt:

LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Alternatively execute this query with mysqli->query:

$mysqli->query( "LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'" );

This is the content inside the CSV file:

"username"
1,"Name Example"
2,"Name Example"
3,"Name Example"

Solve possible LOAD DATA error messages

If you get the following errors:

Syntax error or access violation
The used command is not allowed with this MySQL version
Invalid authorization specification
Access denied for user 'user'@'localhost' (using password: YES)
The used command is not allowed with this MySQL version

Make sure to:

1) Grant the user FILE privileges in MySQL:

GRANT FILE ON *.* TO 'user'@'localhost';

2) Edit the file /etc/mysql/my.cnf and enable local-infile:

[mysql]
local-infile=1
[mysqld]
local-infile=1

3) Edit the php.ini file and add this line:

mysql.allow_local_infile = On

4) The CSV file exists:

LOAD DATA INFILE '".realpath(is_file($file))."' [...]

5) Enable LOAD LOCAL INFILE when connecting from PHP:

$handle = mysqli_init();
mysqli_options($handle, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($handle,server,user,code,database);

6) The CSV file is readable:

chmod 777 file.csv

Updated on March 17, 2014 at 1:31 am

Receive updates via email

Other Posts

Updated Posts