Import CSV into SQL: Database Commands and PHP

Published Updated

To import CSV into a SQL database, parse the file with a CSV reader or the database's bulk loader, check the column mapping, and load the rows into a staging table. Validate the staged data before copying it into the tables your application uses.

Use PHP and PDO for uploads that need application checks. Use a native loader for larger files when you control the file format and database access. Both approaches need a way to reject bad rows and retry without creating duplicates.

Why Import CSV into a Staging Table?

A staging table keeps imported CSV rows separate from application data while you check their types, missing values, and duplicates. Start with a table that records the import batch and original row values:

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.

How Do You Parse CSV Fields in PHP?

Parse CSV with fgetcsv() or SplFileObject::fgetcsv(), which handle quoted fields and embedded separators. Splitting each line on commas does not:

<?php

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

That breaks as soon as a field contains a comma, a quote, or a line break inside an enclosed value. CSV quoting rules allow those characters inside a field.

In PHP, use 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.

How Do You Import CSV with PHP and PDO?

Import CSV with PHP and PDO by reading each record with a CSV parser, validating its fields, and inserting it with a prepared statement. Use a transaction when the batch must commit together. This approach also lets you attach application metadata such as the current account:

<?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 example expects exactly three columns in the order email, full_name, raw_status and skips invalid email addresses. For supplier files, map the header names and keep rejected rows with an error reason. Its line_number counts parsed records, not physical lines when a field contains line breaks. 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.

How Should a PHP Application Handle CSV Uploads?

A PHP CSV upload should use a server-generated filename in a private, writable directory. Check the upload error and size, require an authorized user, then parse the stored file separately from accepting the upload. The example below handles storage; create its private directory first and enforce your upload limits before calling it:

<?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.

Which Native Tools Import CSV into SQL?

MySQL uses LOAD DATA, PostgreSQL uses COPY or the psql \copy command, and SQLite uses its shell’s .import command. SQL Server offers BULK INSERT and SSMS import wizards. 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:

SQLite .import maps fields to every column in the target table. Load this three-column file into a matching temporary table first, then copy it into the six-column staging table:

CREATE TEMP TABLE csv_contacts (
    email TEXT,
    full_name TEXT,
    raw_status TEXT
);
.mode csv
.import --skip 1 contacts.csv csv_contacts
INSERT INTO imported_contacts_stage (email, full_name, raw_status)
SELECT email, full_name, raw_status FROM csv_contacts;
DROP TABLE csv_contacts;

These native-loader examples expect a header and three fields in the stated order. They use the staging table's default batch ID, manual; assign a distinct batch ID when retaining several imports. MySQL's LOCAL option must be permitted by both client and server. In SSMS, review inferred column types before finishing an import, since later rows may contain longer text or different values.

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.

How Do You Validate Imported CSV Rows?

Validate imported CSV rows for required values, valid dates and numbers, duplicates, and matching parent keys before copying them into application tables. Filter each check by its batch ID:

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 how to 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.

Remove or quarantine rejected rows from this staging batch before running the next statement, which copies every row with the selected batch ID. Then copy the remaining rows into your existing contacts table. This example assumes that table has compatible email, full_name, and status columns; bind :batch_id through PDO:

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 records where the file's loose text becomes the application's stricter model.

When Does CSV Data Need a SQL Database?

CSV data needs a SQL database when you want to search, join, deduplicate, or report on its rows. If a user only needs to inspect a file, skip the database import. 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 explains the database connection and prepared statements. PHP handles upload handling, validation messages, and account-level context. SQL owns the staged rows, constraints, joins, and final write.

Can CSV Exports Trigger Spreadsheet Formulas?

CSV exports can trigger spreadsheet formulas when cells begin with formula-triggering characters such as =, +, -, or @. Treat exported values as untrusted even when they were stored safely in SQL.

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

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

Position-Based Mapping Fails Silently

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

A Malformed Row Aborts a Good Import

The import stops partway on a conversion error, leaving some rows written and the rest missing. rows were committed individually or in earlier batches before a later row failed conversion. 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

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

Choose a Loader You Can Validate and Retry

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.

Frequently Asked Questions

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

Handle reordered CSV columns by reading the header and mapping each name to the correct SQL field. Reject files with missing or duplicate required headers. Position-based imports can put plausible values into the wrong columns without reporting an error.

Should CSV dates and numbers be imported as text first?

Import unfamiliar CSV dates and numbers into text columns in a SQL staging table first, then validate and convert them explicitly. Even text columns have size and encoding limits, so keep a record of rejected rows rather than assuming every value will fit.

How large a CSV file can PHP process?

PHP can stream CSV files larger than its memory limit because a CSV reader holds one record at a time. Upload size limits, maximum record size, execution time, and database write speed still constrain the import. Run long imports as background or command-line jobs rather than keeping a web request open.

How do you safely retry a failed CSV import?

Retry a failed CSV import with the same batch identifier and an explicit duplicate-handling rule. Use a unique key with an upsert, skip rows already committed, or replace the failed batch in a transaction. A unique key by itself prevents duplicates but does not automatically update existing rows.

Should a CSV import run inside a SQL transaction?

Use a SQL transaction for a moderate CSV import when the whole batch must succeed or fail together. For large files, smaller committed batches can reduce lock time and transaction-log growth. Record completed batches so a retry does not insert them again; separate batches do not provide whole-file atomicity.

Sources

  1. [1]
    PHP fgetcsv
    (php.net)
  2. [2]
  3. [3]
    PDO::prepare
    (php.net)
  4. [4]
  5. [5]
  6. [6]
  7. [7]
  8. [8]
    PostgreSQL COPY
    (postgresql.org)
  9. [9]
  10. [10]
  11. [11]
    CSV Injection
    (owasp.org)