Solve a CSV import problem in a MySQL table

This guide explains how to solve a .csv file import problem in a MySQL table.The proposed alternative is to read the CSV file line by line using PHP and insert the data into the MySQL database.

"load data local infile" function disabled

The function LOAD DATA LOCAL INFILE allows to import a CSV file into a MySQL table and is now unfortunately frequently used by hackers to get access to certain sites hosted on machines accepting this function.

To combat malicious acts and continue to protect customers' data as much as possible, Infomaniak has deactivated the function LOAD DATA LOCAL INFILE Not affected by this modification are people importing their CSV files (provided they do not check "CSV via LOAD DATA") via phpMyAdmin.

Here is an alternative to continue importing data in CSV format into a MySQL table, a complete example of the code with correct error management for opening the CSV file and inserting the data into the database.

This version uses mysqli to connect to the database and the requests prepared to insert the data, thus providing better security and compatibility with recent versions of PHP and ease of implementing it in your PHP script or simply in a new PHP file in your directory /web:

$NomDuFichier = "data.csv";
// Connexion à la base de données MySQL avec mysqli
$link = new mysqli("localhost", "username", "password", "database");
// Vérification de la connexion
if ($link->connect_error) {
   die("Échec de la connexion : " . $link->connect_error);
}
// Ouverture du fichier CSV en lecture
if (($handle = fopen($NomDuFichier, "r")) !== FALSE) {
   // Décomposition de chaque ligne du fichier CSV
   while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
       // Préparation de la requête SQL
       $query = "INSERT INTO `test` VALUES (" . str_repeat('?,', count($data) - 1) . "?)";
       $stmt = $link->prepare($query);
       // Vérification de la préparation de la requête
       if ($stmt === FALSE) {
           die("Échec de la préparation de la requête : " . $link->error);
       }
       // Liaison des paramètres
       $types = str_repeat('s', count($data));  // Assume que toutes les colonnes sont de type string
       $stmt->bind_param($types, ...$data);
       // Exécution de la requête
       if (!$stmt->execute()) {
           die("Échec de l'exécution de la requête : " . $stmt->error);
       }
       // Fermeture de la déclaration
       $stmt->close();
   }
   // Fermeture du fichier CSV
   fclose($handle);
} else {
   echo "Erreur : impossible d'ouvrir le fichier.
";
   exit(1);
}
// Fermeture de la connexion à la base de données
$link->close();
?>

Get help

Unfortunately, it is not possible to tell you exactly where these lines of code should be added to your script.

If the proposed alternative poses a problem when you submit several CSV files to read and the e.g. procedure ends without sending a message despite the message management points in the PHP file, it is possible that the tables and fields used are poorly indexed and in this case also contact your webmaster.

See PHP documentation for fgetcsv : http://php.net/manual/fr/function.fgetcsv.php