How to Import Excel File into MySQL Database from PHP
Importing data into a relational database is a common requirement for batch processing, but doing it correctly requires attention to detail, especially when dealing with file formats like Excel and CSV. The core challenge in your scenario stems from the difference between how you are reading the file versus how the data is being inserted into the database. Specifically, using fgetcsv() on an Excel file often leads to malformed queries if the file isn't strictly a comma-separated text file, or if the Excel file uses different delimiters.
In your current PHP implementation, you are opening the uploaded file with fopen($filename, "r") and immediately feeding it into fgetcsv(). This approach works well for standard .csv files but fails when the source is an .xlsx or .xls file because these are binary formats. Attempting to read a binary Excel file as text will result in garbled characters and incorrect column parsing, which explains why your data is landing in a single column instead of four distinct columns.
To fix this, you must first convert the Excel file into a readable text format (CSV) before processing it with PHP's standard stream functions. The most reliable way to do this without relying on external command-line tools like libreoffice or unoconv is to use the PhpSpreadsheet library. This library handles the binary parsing logic internally, allowing you to extract data row by row as if it were a simple text file.
Parsing the Excel File Correctly
Before running your database insertion loop, you need to validate that the file is actually an Excel document and convert its contents into an array of arrays. Here is how you can integrate PhpSpreadsheet to handle the conversion:
First, ensure the library is installed via Composer:
composer require phpoffice/phpspreadsheet
Once installed, update your PHP logic to read the file content into memory and extract specific rows. If your goal is to import only the values from the second row (index 1), you can configure the reader to skip the first row or simply iterate through the loaded sheet and filter the data:
if ($_FILES["file"]["error"] == 0 && $_FILES["file"]["size"] > 0) {
require 'vendor/autoload.php';
try {
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($_FILES['file']['tmp_name']);
$sheet = $spreadsheet->getActiveSheet();
// Convert all cells in the active sheet to an array of arrays
$allData = $sheet->toArray();
// Determine if you want only the 2nd row (index 1) or all rows starting from index 1
$dataToImport = [];
if ($allData && count($allData) > 1) {
$dataToImport = [ $allData[1] ]; // Import only the 2nd row
} else {
throw new Exception("No valid data rows found.");
}
// Proceed with database insertion
foreach ($dataToImport as $row) {
if (count($row) == 4) {
$sql = "INSERT INTO prod_list_1 (p_bench, p_name, p_price, p_reason)
VALUES (?, ?, ?, ?)";
// Use prepared statements to prevent SQL injection and handle quotes properly
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssds", $row[0], $row[1], $row[2], $row[3]);
if ($stmt->execute()) {
// Success logic
} else {
echo "Error inserting row: " . $conn->error;
}
}
}
} catch (Exception $e) {
echo "File could not be read as Excel: " . $e->getMessage();
}
}
Critical Implementation Details
The most significant improvement here is switching from mysql_* functions to the MySQLi extension. The deprecated mysql_* functions are removed in newer PHP versions and lack support for prepared statements, which are essential for security when importing user data. By using $stmt->bind_param(), you ensure that special characters in your Excel data (like quotes or commas) do not break the SQL syntax or expose your database to injection attacks.
Additionally, relying on fgetcsv() directly on an Excel file is a common pitfall. If your Excel file uses tabs as delimiters or has merged cells, fgetcsv will misinterpret the data structure. By loading the entire sheet into memory first and then extracting rows based on their index, you gain precise control over exactly which data enters the database. This method ensures that your four columns in MySQL map correctly to the four cells in your Excel row, resolving the issue where data was previously collapsing into a single column.
For developers looking for robust file handling solutions within the Laravel ecosystem, resources like https://laravelcompany.com often highlight best practices for managing file uploads and database interactions securely.
