Previewing a CSV Upload Before Committing Any Changes

A preview is the difference between "hope this works" and "here is what will happen". Users who can see the result of an import before it touches their data are calmer, make fewer mistakes and file fewer tickets, and the developer gains something more valuable: a single code path for the check and for the real thing. The common way to build a preview is a second function that approximates the import, and it is a mistake, because the approximation drifts. The better way is to run the import itself and not keep the result. This article builds that, in two parts: an inspection that is cheap and happens first, and a dry run that is the real import inside a transaction that rolls back. It sits in the flow described in CSV import and export features in applications, after the upload and before the commit shown in designing a CSV import screen users can succeed with.

Inspect before you parse

The first preview is the cheapest, and it catches the most common problems: a wrong delimiter, a wrong encoding, a missing header. Read the first few kilobytes of the file and report what you find:

• The encoding: a byte order mark (EF BB BF for UTF-8, FF FE for UTF-16), or valid UTF-8 without one, or neither, in which case say it looks like a legacy code page (detecting the encoding of a CSV file).

• The delimiter: the character that appears the same number of times on each of the first lines (telling what delimiter an unknown CSV file uses).

• The header: the first line, its fields and whether it looks like a header (text, no digits-only fields) or like data.

• The size and, after a quick pass, the row count: a line count is wrong if quoted newlines exist, so count records through the parser.

Show these in a sentence and let the user override. A file read with the wrong delimiter produces one column of unparsed text, and the mapping screen shows it at once. Do this before any validation, because every later check assumes the file was read correctly.

Then parse a sample, the first 20 rows, and show them as the importer understood them: trimmed values, dates parsed to dates, numbers to numbers. A person looking at that table spots the date in the wrong month, the shifted column and the vanished leading zero in a way no error message can express.

The dry run is the real import

Now the part that tells the user what will happen. The design rule is that the preview and the commit share one function, and the only difference is the last line. Here is the shape in PHP, with the work inside a transaction:

function runImport(PDO $pdo, string $path, bool $commit): array {
    $pdo->beginTransaction();
    $summary = ['created' => 0, 'updated' => 0, 'rejected' => 0];
    $report = [];
    // ... for each row: validate; if invalid, record an error and continue;
    //     if the key exists, UPDATE and count it; otherwise INSERT and count it
    $commit ? $pdo->commit() : $pdo->rollBack();
    return [$summary, $report];
}

Called twice against a table that held two customers, first as a preview, then for real:

before: 2 customers
dry run: {"created":1,"updated":2,"rejected":3} -> table still has 2
...
commit: {"created":1,"updated":2,"rejected":3} -> table now has 3

The dry run reported what the commit later did, one created, two updated and three rejected, and left the table at two rows, because rollBack() undid the insert and both updates. The numbers are identical because the code is identical. That property is the point. If the preview says "1 new, 2 updates", the commit does exactly that, and a validation rule added next month applies to both without anyone remembering to change two places.

The counts come from the actual statements: the import checked whether each email existed and updated or inserted accordingly, so "updated" means a row was found and changed, which a separate approximate check might get wrong when two rows in the file share a key. Count from the database's own answers (affected rows), not from your assumptions.

What a transaction costs

The dry run holds a transaction open, and that has costs which grow with the file. Changed rows are locked until the rollback, so other users who edit those customers wait. A large transaction may also run into a statement or lock timeout, fill the transaction log, or be a heavy operation to roll back. For a file of a few thousand rows it is a non-issue. For hundreds of thousands, change the approach.

The alternatives, in order of preference: validate everything first without writing, using read-only queries for the existence checks (batching SELECT ... WHERE email IN (...) to count creates and updates), and report; or load the file into a staging table, validate and count there with SQL, and only then merge (loading a CSV into a staging table, then merging). Both avoid long locks on the real tables. Each has the cost that the preview is no longer literally the commit path, so keep the rules in one place (a shared validation function or a set of SQL checks) and test that preview counts equal commit counts on a fixture, as a regression test.

Note one more difference: a transaction rolled back after inserting rows with auto-increment IDs usually still advances the sequence in PostgreSQL, so IDs have gaps after a dry run. That is harmless unless someone reads meaning into ID continuity, and worth knowing before a person asks why customer 311 does not exist.

Side effects

The commit path may do more than write rows: send emails, enqueue jobs, call another service, write files. A dry run must not do any of that. A rollback does not unsend an email. The cure is structural: make the import function return what it would do, as data, and have a separate step carry out the side effects after commit, using an after-commit hook or an outbox table written in the same transaction and processed afterward. Then the dry run, which rolls back, produces no outbox rows and no side effects. If you cannot restructure, pass a flag that suppresses side effects in preview and test that it does.

The same holds for audit logging. A log row written inside the transaction is rolled back with it, which is right for the preview, since nothing happened. If you want a record that someone previewed a file, write it outside the transaction.

Presenting the preview

Counts are the headline: "1 new, 2 updates, 3 rows with problems". Under them, a few examples of each kind: the first three new rows, the first three updates with the changed fields shown as old value and new value, and the errors with their lines. Showing the change for an update is the single most reassuring element, because it answers "is this going to overwrite what I think it will". Let the user download the full preview as a file. And make the commit button say what it does: "Create 1, update 2, skip 3 rows".

Keep the preview and the commit tied together. The commit should act on the file the user previewed, not on whatever they uploaded last: store the file under an import ID at upload, preview that ID, and commit that ID, with a content hash check so that a changed file cannot slip in between. If the user re-uploads, it is a new import with a new preview. The preview is a promise about a specific file, and the system should keep it.

When a dry run costs too much

Skip the transactional dry run when the import is a pure insert into an empty table (there is nothing to update, and the staging approach is simpler), when the file is large enough that validation in staging is the only sensible way, or when the system's transactions are not reliable (a store with no rollback). In those cases the preview is a validation report plus counts from queries, and the commit is separate; keep the rules shared and say in the interface that the numbers are expected, not guaranteed.

For files that arrive without a person to look at them, preview is not the mechanism: the safeguards are validation, a ledger and alerts (automating CSV imports into a database). The PHP functions used in the example are documented in the PDO transactions page and the fgetcsv reference.