Import Excel or CSV Data into MySQL with PHP (Safely, at Scale)

Bulk importing a spreadsheet into MySQL is a standard admin feature and a standard source of production incidents. Done casually it is an SQL injection hole, an arbitrary file upload, and an out-of-memory crash all at once.

This guide builds a CSV and Excel import in PHP that handles real files safely: validating uploads, streaming rather than buffering, inserting in batches, and reporting exactly which rows failed and why.

The pipeline

What the old approach gets wrong

The version that circulates widely — and that this article previously showed — looks roughly like this:

// DO NOT USE
$conn = mysql_connect($hostname, $username, $password);
move_uploaded_file($_FILES['file']['tmp_name'], "upload_csv1/" . $_FILES['file']['name']);
$sql = "insert into excel1 set id='" . $line[1] . "', image='" . $line[16] . "'";
mysql_query($sql);

Four separate failures:

• The mysql_* extension was removed in PHP 7. This code does not run on any supported PHP version. Use PDO or mysqli.
• SQL injection. Cell values go straight into the query. A CSV containing ', image='x rewrites your statement — and CSV files are exactly the sort of thing users hand you.
• Arbitrary file upload. Saving $_FILES['file']['name'] verbatim into a web-served directory means someone uploads shell.php and requests it. That is remote code execution.
• ini_set('upload_max_filesize', …) does nothing. It is PHP_INI_PERDIR — it can only be set in php.ini or .htaccess, never at runtime. The line gives false confidence.

Handling the upload

<?php
declare(strict_types=1);

const MAX_UPLOAD_BYTES = 20 * 1024 * 1024;   // 20 MB
const ALLOWED_EXT = ['csv', 'xlsx'];

function receiveUpload(array $file): string
{
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
        throw new RuntimeException('Upload failed with error code ' . $file['error']);
    }

    if ($file['size'] > MAX_UPLOAD_BYTES) {
        throw new RuntimeException('File exceeds the 20 MB limit.');
    }

    $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
    if (!in_array($ext, ALLOWED_EXT, true)) {
        throw new RuntimeException('Only .csv and .xlsx files are accepted.');
    }

    // Trust the file's own bytes, not the extension or the browser-sent MIME type.
    $mime = (new finfo(FILEINFO_MIME_TYPE))->file($file['tmp_name']);
    $okMime = ['text/plain', 'text/csv', 'application/csv', 'application/zip',
               'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
    if (!in_array($mime, $okMime, true)) {
        throw new RuntimeException("Unexpected file content type: {$mime}");
    }

    // Store outside the document root under a generated name. Never reuse the
    // client-supplied filename — it is attacker-controlled.
    $destination = sys_get_temp_dir() . '/import_' . bin2hex(random_bytes(16)) . '.' . $ext;

    if (!move_uploaded_file($file['tmp_name'], $destination)) {
        throw new RuntimeException('Could not store the uploaded file.');
    }

    return $destination;
}

The two rules that matter: generate the filename yourself, and store it where the web server will not execute it. An .xlsx is a ZIP archive, which is why application/zip appears in the allowed list.

Reading the file

For CSV, fgetcsv() reads one row at a time, so a 500 MB file uses the same memory as a 5 KB one:

function readCsv(string $path): Generator
{
    $handle = fopen($path, 'r');
    if ($handle === false) {
        throw new RuntimeException('Could not open the file.');
    }

    // Strip the UTF-8 BOM Excel adds, or the first column name arrives corrupted.
    $bom = fread($handle, 3);
    if ($bom !== "\xEF\xBB\xBF") {
        rewind($handle);
    }

    $headers = fgetcsv($handle);
    if ($headers === false) {
        throw new RuntimeException('The file appears to be empty.');
    }
    $headers = array_map(static fn ($h) => strtolower(trim((string) $h)), $headers);

    $lineNumber = 1;
    while (($row = fgetcsv($handle)) !== false) {
        $lineNumber++;

        if ($row === [null] || $row === []) {
            continue;   // blank line
        }

        if (count($row) !== count($headers)) {
            yield $lineNumber => ['__error' => 'Column count does not match the header row.'];
            continue;
        }

        yield $lineNumber => array_combine($headers, $row);
    }

    fclose($handle);
}

Returning a Generator is what keeps memory flat — the caller iterates rows without the full file ever existing in an array.

The BOM handling is not optional. Excel writes those three bytes at the start of every CSV it saves as UTF-8, and without stripping them your first header reads as \xEF\xBB\xBFid rather than id, so every lookup for that column silently fails.

Real Excel files

An .xlsx is a ZIP of XML, not something to parse by hand. Use the standard library:

composer require phpoffice/phpspreadsheet

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

function readXlsx(string $path): Generator
{
    $reader = new Xlsx();
    $reader->setReadDataOnly(true);   // skip styles and formatting — far faster
    $sheet = $reader->load($path)->getActiveSheet();

    $headers = [];
    foreach ($sheet->getRowIterator() as $row) {
        $cells = [];
        foreach ($row->getCellIterator() as $cell) {
            $cells[] = $cell->getFormattedValue();
        }

        if ($headers === []) {
            $headers = array_map(static fn ($h) => strtolower(trim((string) $h)), $cells);
            continue;
        }

        yield $row->getRowIndex() => array_combine($headers, array_pad($cells, count($headers), null));
    }
}

Use getFormattedValue() rather than getValue() for dates — Excel stores them as serial numbers, and the raw value will hand you 45123 instead of a date.

Validating and inserting

function importRows(PDO $pdo, iterable $rows, int $batchSize = 500): array
{
    $sql = 'INSERT INTO products (sku, name, price, stock)
            VALUES (:sku, :name, :price, :stock)
            ON DUPLICATE KEY UPDATE
                name = VALUES(name), price = VALUES(price), stock = VALUES(stock)';

    $stmt = $pdo->prepare($sql);

    $imported = 0;
    $errors = [];
    $inBatch = 0;

    $pdo->beginTransaction();

    foreach ($rows as $lineNumber => $row) {
        if (isset($row['__error'])) {
            $errors[] = "Row {$lineNumber}: {$row['__error']}";
            continue;
        }

        $error = validateRow($row);
        if ($error !== null) {
            $errors[] = "Row {$lineNumber}: {$error}";
            continue;
        }

        try {
            $stmt->execute([
                ':sku'   => trim($row['sku']),
                ':name'  => trim($row['name']),
                ':price' => (float) $row['price'],
                ':stock' => (int) $row['stock'],
            ]);
            $imported++;
        } catch (PDOException $e) {
            $errors[] = "Row {$lineNumber}: " . $e->getMessage();
            continue;
        }

        // Commit periodically so one enormous transaction does not blow up
        // the InnoDB log or hold locks for the whole run.
        if (++$inBatch >= $batchSize) {
            $pdo->commit();
            $pdo->beginTransaction();
            $inBatch = 0;
        }
    }

    $pdo->commit();

    return ['imported' => $imported, 'errors' => $errors];
}

function validateRow(array $row): ?string
{
    if (trim((string) ($row['sku'] ?? '')) === '') {
        return 'SKU is required.';
    }
    if (!is_numeric($row['price'] ?? null)) {
        return 'Price must be a number.';
    }
    if ((float) $row['price'] < 0) {
        return 'Price cannot be negative.';
    }
    return null;
}

Three decisions worth calling out.

The prepared statement is created once and executed many times. That is both the injection fix and a genuine performance win — MySQL parses the query a single time.

ON DUPLICATE KEY UPDATE makes the import idempotent. Re-running the same file updates existing rows instead of failing on a unique constraint, which matters because people always upload the same file twice.

Bad rows are collected, not fatal. Aborting on row 4,300 of 5,000 leaves the operator with no idea what went wrong. Reporting every failure at the end lets them fix the spreadsheet once.

Do not use TRUNCATE first

The original code called TRUNCATE before importing. If the upload then fails halfway, the previous data is gone and TRUNCATE cannot be rolled back — it implicitly commits. Import into the live table with upserts, or stage into a temporary table and swap only after a clean run.

Very large files

Beyond roughly 100,000 rows, do not run the import inside a web request. Browsers time out, users refresh and trigger a second concurrent import, and PHP's max_execution_time will cut you off mid-run.

Store the upload, queue a job, and let the operator watch progress. If you genuinely need raw speed and the file is already clean, MySQL's own loader is dramatically faster than any PHP loop:

LOAD DATA LOCAL INFILE '/tmp/import.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(sku, name, price, stock);

It needs local_infile enabled on both server and client, and it gives you no per-row validation — so use it only for data you have already checked.

Checklist

• Use PDO or mysqli with prepared statements — never string-concatenate cell values.
• Generate your own filename and store uploads outside the document root.
• Verify content type with finfo, not the extension or the browser's claim.
• Strip the UTF-8 BOM before reading headers.
• Stream with a generator; never load the whole file into an array.
• Commit in batches rather than one giant transaction.
• Collect row-level errors and show them all at the end.
• Delete the temporary file when the run finishes, successfully or not.