Import CSV into SQL: Load, Validate, and Transaction Paths

CSV is the delivery format for a batch of text rows. SQL is where the data starts having memory, rules, relationships, and consequences.

Treat the file as an import pipeline rather than a quick string conversion. Parse the CSV with a real parser, load it into a staging table, validate it before touching the real tables, and use prepared statements or a database-native bulk loader depending on where the file comes from and how much review it needs.

The problem is ordinary and it does not go away: someone has a spreadsheet export and needs it inside a database, and the next person inherits product data, contact lists, or supplier exports from another system. The import that looks like a one-off script is usually the first version of a data pipeline.

Start with a Staging Table

Do not import a stranger's spreadsheet straight into the table your application depends on. The safer first step is a holding area:

CREATE TABLE imported_contacts_stage (
    batch_id VARCHAR(36) NOT NULL DEFAULT 'manual',
    line_number INTEGER,
    email VARCHAR(255),
    full_name VARCHAR(255),
    raw_status VARCHAR(100),
    imported_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

That table has a batch, a source line number, and columns close to the CSV file. It remains a staging model rather than the final data model. It is a place to inspect, reject, deduplicate, and transform rows before they get near contacts, users, orders, or whatever else your application trusts.

The defaults keep the table friendly to database-native bulk loaders. In a real application, add the key or unique constraint that matches your database and import process.

This is the same discipline as schema design basics, only applied to incoming data. A CSV file often has no real types, no foreign keys, no useful constraints, and no record of why row 418 looks different from row 417. A staging table gives you somewhere to find out.

Parse the File Correctly

The first mistake is still the oldest one:

<?php

$columns = explode(",", $line);

That breaks as soon as a field contains a comma, a quote, or a line break inside an enclosed value. RFC 4180 exists because CSV is simple in the way a door lock is simple: the shape is small, but the details matter.

In PHP, the two boring tools are fgetcsv() and SplFileObject::fgetcsv():

<?php

$file = new SplFileObject(__DIR__ . "/contacts.csv", "rb");
$file->setFlags(SplFileObject::READ_CSV | SplFileObject::SKIP_EMPTY);
$file->setCsvControl(",", "\"", "");

foreach ($file as $row) {
    if ($row === [null] || $row === false) {
        continue;
    }

    // Validate and import the parsed row here.
}

The empty escape string is there on purpose. PHP 8.4 deprecated relying on the default escape value for CSV parsing, and the PHP manual recommends setting it explicitly when round-tripping RFC 4180-style files matters.

If the upload can be large, keep this streaming shape. Do not read the whole file into an array just because the first test file had twelve rows.

Import with PHP and PDO

Use a PHP import loop when the file is uploaded by a user, needs row-by-row validation, or must attach application metadata such as the current account. The database still receives values through a prepared statement:

<?php

function importContacts(PDO $pdo, string $path, string $batchId): int
{
    $file = new SplFileObject($path, "rb");
    $file->setFlags(SplFileObject::READ_CSV | SplFileObject::SKIP_EMPTY);
    $file->setCsvControl(",", "\"", "");

    $insert = $pdo->prepare(
        "INSERT INTO imported_contacts_stage
            (batch_id, line_number, email, full_name, raw_status)
         VALUES
            (:batch_id, :line_number, :email, :full_name, :raw_status)"
    );

    $imported = 0;
    $lineNumber = 0;

    $pdo->beginTransaction();

    try {
        foreach ($file as $row) {
            $lineNumber++;

            if ($row === [null] || $row === false) {
                continue;
            }

            if ($lineNumber === 1 && strtolower((string) $row[0]) === "email") {
                continue;
            }

            [$email, $name, $status] = array_pad($row, 3, "");

            $email = trim((string) $email);

            if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                continue;
            }

            $insert->execute([
                "batch_id" => $batchId,
                "line_number" => $lineNumber,
                "email" => strtolower($email),
                "full_name" => trim((string) $name),
                "raw_status" => trim((string) $status),
            ]);

            $imported++;
        }

        $pdo->commit();
    } catch (Throwable $error) {
        $pdo->rollBack();
        throw $error;
    }

    return $imported;
}

This is not the fastest possible loader. It is the clearer loader when each row needs application judgment. Prepared statements keep CSV values out of raw SQL strings, and the transaction keeps a failed import from leaving half a batch in the staging table.

For a very large upload, commit in batches and record enough progress to resume or retry safely. That is a product decision, not a loop trick.

Upload Handling

The old PHP CSV Importer snippet mixed upload handling and database inserts in one useful but fragile file. Keep upload handling and database inserts separate. First accept the upload, give it a server-owned name, and store it outside the public document root:

<?php

function storeUploadedCsv(array $upload): string
{
    if (($upload["error"] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
        throw new RuntimeException("CSV upload failed.");
    }

    $tmpName = (string) ($upload["tmp_name"] ?? "");

    if ($tmpName === "" || !is_uploaded_file($tmpName)) {
        throw new RuntimeException("Upload did not come through PHP.");
    }

    $targetDir = __DIR__ . "/../storage/imports";
    $targetPath = $targetDir . "/" . bin2hex(random_bytes(16)) . ".csv";

    if (!move_uploaded_file($tmpName, $targetPath)) {
        throw new RuntimeException("Could not store uploaded CSV.");
    }

    return $targetPath;
}

The browser's filename is metadata, not a path. Store it if you need to show it back to an admin, but do not use it to decide where the file lands. After this step, call the importer with the stored path:

<?php

$path = storeUploadedCsv($_FILES["contacts_csv"]);
$count = importContacts($pdo, $path, bin2hex(random_bytes(8)));

That small boundary is the difference between "a CSV importer" and "a web form that lets strangers pick filenames on your server." The parser, staging table, and validation queries still do the real import work.

Native Loaders

MySQL, PostgreSQL, and SQLite all have import tools that can move data faster than a PHP row loop. Use them when the file is already on a trusted path, the schema is understood, and the import is more operational than interactive.

For MySQL, the native loader is LOAD DATA:

LOAD DATA LOCAL INFILE 'contacts.csv'
INTO TABLE imported_contacts_stage
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(email, full_name, raw_status);

PostgreSQL uses COPY, or \copy from psql when the client should read the file:

\copy imported_contacts_stage (email, full_name, raw_status)
FROM 'contacts.csv'
WITH (FORMAT csv, HEADER true);

SQLite's command-line shell has .mode csv and .import for the same job:

.mode csv
.import --skip 1 contacts.csv imported_contacts_stage

Those loaders have their own file access and privilege rules. Read the database manual for the environment you are running. A local maintenance script and a web upload endpoint have very different risk profiles.

Validate Before Final Tables

Once the rows are staged, run SQL against the batch before promoting anything:

SELECT line_number, email
FROM imported_contacts_stage
WHERE batch_id = :batch_id
  AND (email IS NULL OR email NOT LIKE '%@%');

That query is not a complete email validator. It is an import review query that shows the pattern: find bad rows while they are still cheap to reject.

These checks usually belong at this layer:

• Required fields are present.
• Dates parse into the format your database expects.
• Numeric columns do not contain currency symbols or notes.
• Lookup values match known application values.
• Duplicates are visible before unique constraints reject them.
• Foreign keys can be resolved to real parent rows.

After the batch passes review, move clean rows into the real table with SQL that names the transformation:

INSERT INTO contacts (email, full_name, status)
SELECT
    LOWER(email),
    NULLIF(full_name, ''),
    COALESCE(NULLIF(raw_status, ''), 'new')
FROM imported_contacts_stage
WHERE batch_id = :batch_id;

That statement is small, but it is doing important work. It records where the file's loose text becomes the application's stricter model.

Preview vs Import

Sometimes the right first step is not a database import. The safer first move is a small, honest preview. If a user only needs to inspect a file, the CSV to HTML table guide is the lighter tool.

Once the rows need search, joins, deduplication, ownership, or reporting, move the work into SQL. Follow the SQL indexes guide when imported data becomes searchable, and use transactions and ACID when the import touches more than one table.

If the import lives in a PHP application, PHP and MySQL with PDO is the handoff point. PHP owns upload handling, validation messages, and account-level context. SQL owns the staged rows, constraints, joins, and final write.

Watch Exports Too

Importing CSV into SQL is one boundary. Exporting SQL data back to a spreadsheet is another. OWASP documents CSV injection for cells that begin with formula-triggering characters such as =, +, -, or @.

That does not mean every import must rewrite user data. It means exports need a deliberate policy before someone opens the file in a spreadsheet. Imports, previews, and exports are related, but they are not the same feature.

Common Pitfalls & Debugging

Splitting on Commas Breaks Quoted Fields

Symptom: most rows import cleanly, then a few land with every field shifted one column right. Cause: explode(",", $line) cannot tell a comma inside a quoted field from a separator, so any address or note containing one produces extra fields. Fix: read with fgetcsv, which understands quoting.

Position-Based Mapping Fails Silently

Symptom: an import that ran for months starts writing plausible values into the wrong columns, with no error. Cause: the code maps fields by position and the supplier reordered the export, so the field count still looks right. Fix: map column names from the header row, and reject files missing an expected name.

A Malformed Row Aborts a Good Import

Symptom: the import stops partway on a conversion error, leaving some rows written and the rest missing. Cause: rows were committed individually or in earlier batches before a later row failed conversion. Fix: wrap the run in a transaction or record batch identifiers, and stage raw text before any validated conversion.

A Re-run Doubles the Rows That Already Landed

Symptom: after a failed import is re-run, the table holds two copies of every row from the first attempt. Cause: the write had no unique key and no run identifier, so the second pass re-inserted committed rows. Fix: give each run an identifier and make the write idempotent against a unique key.

The Practical Rule

Use PHP and PDO when the import is part of the application. Use LOAD DATA, COPY, or SQLite .import when the file is trusted and the job is operational. In both paths, keep the staging table in the design.

A one-file converter is useful because it gets data moving. The version worth keeping needs one more step of patience: let the file land somewhere temporary, then let the database rules earn their keep.

This example has been updated from the original CodeWalkers CSV-to-SQL scripts, which read a spreadsheet export and wrote it straight into the live MySQL table as concatenated SQL. The staging table and the native-loader options here are what that missing step became.

Frequently Asked Questions

How do you handle CSV files whose columns arrive in a different order?

Read the header row and map column names to fields rather than relying on position. Reject the file when an expected name is missing. Position-based imports fail silently when a supplier reorders columns, which puts good-looking data in the wrong fields.

Should you import dates and numbers as text first?

Into a staging table, yes. Text columns accept the whole file so you can inspect what actually arrived, then convert with explicit rules. Converting during the read means one malformed row can abort an otherwise good import.

How large a CSV can PHP process?

Size is rarely the limit when you stream the file line by line with fgetcsv, because memory holds one row at a time. The practical constraints are the script time limit and the database write rate, not the file size.

How do you safely re-run an import that failed halfway?

Give each import run an identifier, and make the write idempotent with a unique key so repeated rows update rather than duplicate. Then a re-run either resumes or replaces cleanly instead of doubling the rows that already landed.

Should an import run inside a transaction?

For moderate files yes, as shown above. For very large files one transaction bloats the redo log and holds locks far longer than needed, so commit per batch and record which batch identifiers succeeded. A retry then skips completed work instead of replaying the whole file.