Import Excel Data into a MySQL Database Using PHP

zeroexp.dev ZeroonLeave a Comment on Import Excel Data into a MySQL Database Using PHP
Import Excel Data into a MySQL Database Using PHP

Introduction

Importing data from Excel into MySQL is a common task in applications requiring bulk data uploads, reports, and automation. Instead of manual entry, PHP can process Excel files and insert structured data into a database.

This guide covers:

  • Installing PhpSpreadsheet
  • Loading and reading Excel files in PHP
  • Extracting and validating Excel data
  • Inserting structured data into a MySQL database
  • Handling large datasets efficiently

1. Installing PhpSpreadsheet for Excel Handling

PhpSpreadsheet allows PHP to read, write, and manipulate Excel files. Install it using Composer:

composer require phpoffice/phpspreadsheet

Include PhpSpreadsheet in your PHP script:

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

At this point, your application is ready to process Excel files.

2. Preparing the MySQL Database

Create a MySQL database and a table to store imported data.

CREATE DATABASE excel_import_db;

USE excel_import_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT NOT NULL,
    city VARCHAR(100) NOT NULL
);

The table includes columns for user ID, name, email, age, and city.

3. Uploading and Reading an Excel File in PHP

Create a file upload form to allow users to select an Excel file.

<form action="import.php" method="post" enctype="multipart/form-data">
    <input type="file" name="excel_file" accept=".xls,.xlsx" required>
    <button type="submit">Upload and Import</button>
</form>

4. Processing the Uploaded Excel File in PHP

In import.php, process the uploaded file and extract its contents.

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

if ($_FILES['excel_file']['error'] === 0) {
    $filePath = $_FILES['excel_file']['tmp_name'];

    $spreadsheet = IOFactory::load($filePath);
    $worksheet = $spreadsheet->getActiveSheet();
    
    foreach ($worksheet->getRowIterator(2) as $row) { // Skip header row
        $cells = $row->getCellIterator();
        $cells->setIterateOnlyExistingCells(true);
        
        $data = [];
        foreach ($cells as $cell) {
            $data[] = $cell->getValue();
        }

        insertDataIntoDatabase($data);
    }
    
    echo "Excel data imported successfully.";
} else {
    echo "File upload failed.";
}

5. Inserting Excel Data into MySQL

Define a function to insert data into the database.

function insertDataIntoDatabase($data) {
    $conn = new mysqli("localhost", "root", "", "excel_import_db");

    if ($conn->connect_error) {
        die("Database connection failed: " . $conn->connect_error);
    }

    $stmt = $conn->prepare("INSERT INTO users (name, email, age, city) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("ssis", $data[0], $data[1], $data[2], $data[3]);
    
    $stmt->execute();
    $stmt->close();
    $conn->close();
}

This function establishes a database connection, prepares the SQL statement, and inserts each row of data into MySQL.

6. Handling Duplicate and Invalid Entries

To prevent duplicate entries, modify the SQL query to ignore duplicate emails.

$stmt = $conn->prepare("INSERT IGNORE INTO users (name, email, age, city) VALUES (?, ?, ?, ?)");

For validating age as a numeric value, modify the insertDataIntoDatabase() function:

if (!filter_var($data[2], FILTER_VALIDATE_INT)) {
    return;
}

7. Importing Large Excel Files Efficiently

For large datasets, use a streaming reader to avoid memory overload.

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$reader = new Xlsx();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($filePath);

This ensures only relevant data is processed, reducing memory usage.

8. Displaying Imported Data on a Web Page

After importing, display the stored data in a table.

$conn = new mysqli("localhost", "root", "", "excel_import_db");
$result = $conn->query("SELECT * FROM users");

echo "<table border='1'><tr><th>ID</th><th>Name</th><th>Email</th><th>Age</th><th>City</th></tr>";

while ($row = $result->fetch_assoc()) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['email']}</td><td>{$row['age']}</td><td>{$row['city']}</td></tr>";
}

echo "</table>";

9. Allowing Users to Download Data as Excel

Provide an export function so users can download stored data in Excel format.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Email');
$sheet->setCellValue('D1', 'Age');
$sheet->setCellValue('E1', 'City');

$rowIndex = 2;
$result = $conn->query("SELECT * FROM users");

while ($data = $result->fetch_assoc()) {
    $sheet->setCellValue("A$rowIndex", $data['id']);
    $sheet->setCellValue("B$rowIndex", $data['name']);
    $sheet->setCellValue("C$rowIndex", $data['email']);
    $sheet->setCellValue("D$rowIndex", $data['age']);
    $sheet->setCellValue("E$rowIndex", $data['city']);
    $rowIndex++;
}

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="exported_users.xlsx"');

$writer = new Xlsx($spreadsheet);
$writer->save("php://output");

Best Practices for Importing Excel Data into MySQL

  • Validate data before inserting it into the database.
  • Use INSERT IGNORE or ON DUPLICATE KEY UPDATE to handle duplicate records.
  • Optimize memory usage with setReadDataOnly() for large Excel files.
  • Use prepared statements to prevent SQL injection attacks.
  • Provide a file upload limit to prevent server overload.

Conclusion

Using PhpSpreadsheet, PHP can read Excel files and insert structured data into MySQL. This approach simplifies bulk data imports, automated reporting, and data processing for web applications.

This guide covered:

  • Uploading and reading Excel files in PHP
  • Extracting and validating Excel data
  • Inserting structured data into MySQL
  • Handling large datasets efficiently
  • Displaying and exporting database records in Excel

By implementing these techniques, you can streamline Excel-based data processing in your PHP applications.

Leave a Reply