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
Other Posts
- Detect VMWare Virtual Machine
- Detect Microsoft Virtual PC Virtual Machine
- Fix MariaDB 10.5 "Can't create test file" errors
- Use MariaDB and MySQL without Password (MySQL Backup)
- Check if a Trademark is already registered
- Make name server address permanent in /etc/resolv.conf
- InnoSetup error the servicemanager is not available
- InnoSetup disable DesktopIcon via command-line
Updated Posts
- Delphi 10 Berlin System.Hash MD5 SHA1 SHA2 Hash
- Use cURL to check for SSL certificate issues:
- How to test Socks5 proxy with cURL
- How to use variables in a sed command
- Best Socks5 Servers for Linux Debian
- Error NO_PUBKEY in Google Cloud Debian Packages Update
- How to Run a Command with Time Limit in Bash Linux
- MySQL InnoDB log sequence number is in the future