PHP MySQL CSV Import Tutorial with Secure Code & Best Practices

Learn how to securely import CSV files into MySQL using PHP (MySQLi/PDO). Step-by-step guide with file validation, transactions, error handling, chunk uploads, and LOAD DATA INFILE for large datasets.

If you are working with PHP and MySQL, you will often face a situation where you need to import bulk data from a CSV (Comma Separated Values) file into your database. For example, uploading product lists, user data, or transaction records in one go.

In this tutorial, I will explain how to create a production-ready CSV import system in PHP with proper file validation, database insertion, and security best practices. After reading this, you will be able to implement it in your live projects without issues.

1. Environment Setup

To follow this tutorial, you will need:

• PHP 7.4+ (works with PHP 8 as well)
• MySQL Database
• Local server (XAMPP, WAMP, or MAMP)
• A sample CSV file with some records

2. Database Setup

First, let’s create a MySQL table where we will insert CSV data.

CREATE TABLE IF NOT EXISTS `csvtbl`(
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`)
);

This table has three columns: ID, name, and city.

3. Sample CSV File

Create a CSV file named sample.csv with the following data:

name,city
John Doe,New York
Jane Smith,London
Amit Kumar,Delhi

4. File Upload Form

We will create a simple HTML form to upload the CSV file:

<form action="import.php" method="post" enctype="multipart/form-data">
  <input type="file" name="csv_file" accept=".csv" required>
  <button type="submit" name="import">Upload & Import</button>
</form>

5. Import Script (PDO Version)

Now, let’s write the import.php script which will handle:

• Validating the file
• Reading CSV line by line
• Inserting records into MySQL using prepared statements

<?php
if (isset($_POST['import'])) {
    // Database connection (PDO)
    $dsn = "mysql:host=localhost;dbname=testdb;charset=utf8mb4";
    $username = "root";
    $password = "";

    try {
        $pdo = new PDO($dsn, $username, $password, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);
    } catch (PDOException $e) {
        die("Database connection failed: " . $e->getMessage());
    }

    // File validation
    if ($_FILES['csv_file']['error'] == 0) {
        $fileName = $_FILES['csv_file']['name'];
        $fileTmp = $_FILES['csv_file']['tmp_name'];
        $fileExt = pathinfo($fileName, PATHINFO_EXTENSION);

        if (strtolower($fileExt) !== 'csv') {
            die("Only CSV files are allowed.");
        }

        // Open file
        if (($handle = fopen($fileTmp, "r")) !== FALSE) {
            $isFirstRow = true;
            $stmt = $pdo->prepare("INSERT INTO csvtbl (name, city) VALUES (?, ?)");

            // ✅ Use Transaction for Performance
            $pdo->beginTransaction();

            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                if ($isFirstRow) {
                    $isFirstRow = false; // Skip header
                    continue;
                }

                // Clean values
                $name = trim($data[0]);
                $city = trim($data[1]);

                if ($name != "" && $city != "") {
                    $stmt->execute([$name, $city]);
                }
            }

            $pdo->commit();
            fclose($handle);
            echo "CSV Imported Successfully!";
        }
    } else {
        echo "Error uploading file.";
    }
}
?>

6. Import Script (MySQLi Version)

If you prefer MySQLi, here’s a similar script:

$conn = new mysqli("localhost", "root", "", "testdb");

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

if (($handle = fopen($_FILES['csv_file']['tmp_name'], "r")) !== FALSE) {
    $isFirstRow = true;
    $conn->begin_transaction();
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if ($isFirstRow) {
            $isFirstRow = false;
            continue;
        }
        $name = $conn->real_escape_string(trim($data[0]));
        $city = $conn->real_escape_string(trim($data[1]));

        if ($name != "" && $city != "") {
            $conn->query("INSERT INTO csvtbl (name, city) VALUES ('$name','$city')");
        }
    }
    $conn->commit();
    fclose($handle);
    echo "CSV Imported Successfully!";
}

7. Advanced Performance Techniques

7.1 Split Huge CSV Files into Chunks

If your CSV file has millions of rows, split it into smaller chunks (e.g., 50,000 rows per file) before importing:

$maxLines = 50000;
$lineCount = 0;
$fileIndex = 1;
$output = null;

if (($handle = fopen($fileTmp, "r")) !== FALSE) {
    while (($line = fgets($handle)) !== FALSE) {
        if ($lineCount % $maxLines === 0) {
            if ($output) fclose($output);
            $output = fopen("part_" . $fileIndex++ . ".csv", "w");
        }
        fwrite($output, $line);
        $lineCount++;
    }
    if ($output) fclose($output);
    fclose($handle);
}

7.2 Use LOAD DATA INFILE for Massive Imports

For enterprise-level imports, MySQL provides a built-in command that is much faster:

LOAD DATA INFILE '/path/to/sample.csv'
INTO TABLE csvtbl
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, city);

In PHP, you can trigger this:

$pdo->exec("LOAD DATA INFILE '/var/www/html/uploads/sample.csv'
INTO TABLE csvtbl
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, city)");

8. Common Issues & Fixes

• Encoding Issues: Save file as UTF-8.
• Timeout: Increase max_execution_time in php.ini.
• Duplicate Records: Use INSERT IGNORE or ON DUPLICATE KEY UPDATE.

9. Conclusion

We have successfully built a secure PHP MySQL CSV Import system. You learned:

• How to create a MySQL table for CSV data
• How to upload and validate CSV files
• How to read CSV using PHP fgetcsv()
• How to insert records safely into MySQL
• How to handle large files with transactions, chunking, and LOAD DATA INFILE

Now you can easily use this in your real projects for bulk data import. 🚀