1000 FAQs, 500 tutorials and explanatory videos. Here, there are only solutions!
Solving a CSV import issue into a MySQL table
This guide explains how to resolve an issue with importing a .csv file into a MySQL table. The proposed alternative involves reading the CSV file line by line using PHP and inserting the data into the MySQL database.
"load data local infile" Function Disabled
The "LOAD DATA LOCAL INFILE" function allows importing a CSV file into a MySQL table and unfortunately is now frequently used by hackers to gain access to certain sites hosted on machines that accept this function.
To combat malicious acts and continue to protect customer data as much as possible, Infomaniak has disabled the "LOAD DATA LOCAL INFILE" function. This change does not affect individuals importing their CSV files (provided they do not check "CSV via LOAD DATA") via phpMyAdmin.
Here's an alternative to continue importing CSV data into a MySQL table, with an example in a few easy-to-implement PHP lines in your PHP script or simply in a new PHP file in your /web directory:
// The code above is provided as an example for you to understand how to replace this function; feel free to develop your own more efficient method for inserting data.
$FileName = "data.csv";
// Opening the csv file for reading
$handle = fopen($FileName, "r");
if ($handle) {
// Parsing each line of the csv file
while (($data = fgetcsv($handle, 1000, ";", """)) !== FALSE) {
$num = count($data)-1;
// Generating the SQL query
$query = "INSERT INTO `test` VALUES (";
for ($c=0; $c < $num; $c++) {
$query .= "'" . mysql_real_escape_string($data[$c]) ."',";
}
$query .= "'" . mysql_real_escape_string($data[$num]) ."')";
// Inserting the row into the MySQL database
mysql_query($query, $link) or die (mysql_error());
$query = NULL;
}
// Closing the csv file
fclose($handle);
} else {
echo "Erreur: impossible d'ouvrir le fichier.
exit(1);
}
?>
Unfortunately, it is impossible to specify exactly where in your script these lines of code should be added.
⚠️ For additional help contact a partner or fill out a call for tenders — also read the role of the host.
If the proposed alternative causes problems when submitting multiple CSV files to read and the procedure ends without returning a message despite the message handling points in the PHP file, it is possible that the tables and fields used are incorrectly indexed. In this case, also contact your webmaster.
Also check the PHP documentation for fgetcsv here: http://php.net/manual/fr/function.fgetcsv.php