CSV Import and Export Features in Applications Done Right

Almost every business application gets two requests within its first year: "can I upload a spreadsheet to create these in bulk" and "can I download this list as a CSV". They sound small. The first turns out to be a data-quality problem with a user interface, and the second a performance problem that appears the day a customer has enough rows. Both are shaped by the same fact: a CSV is a file written by humans and other programs, in whatever dialect they like, and an application that treats it as a clean table will find out otherwise from the first user. This guide describes the import and export features I would build, stage by stage, with real runs of the parts that can be tested without a framework. It covers the design of the import screen, previews and dry runs, error reports, streaming and background exports, column names, round trips and the safety checks, and it points to the focused article for each. The back-end side of repeated imports from a partner is a different problem, covered in automating CSV imports into a database.

Import is a pipeline, not a button

The version of import that most teams build first is a form with a file field and a submit button. It reads the file, inserts rows and shows "done", and it fails in a way users cannot understand the first time a cell contains a surprise. The version that works has stages, and each stage gives the user a chance to stop or fix something before the next:

Every article in this cluster is one stage. Designing a CSV import screen users can succeed with covers the screens, previewing a CSV upload before committing the dry run, and reporting row-level errors back to the user the report. The point of the table is the order. Validation comes before commit, and nothing is saved until the user has seen what will happen.

A real import: validate, dry run, report

Here is the core of such a pipeline in plain PHP with SQLite, small enough to read in a minute. The input is a file of six customers, two of whom already exist, with one row that has an unknown plan and no seat count, one with a bad email and one with a plan the application does not offer:

Email,Full name,Plan,Seats
[email protected],Ada Lovelace,pro,5
[email protected],Bob Stone,free,1
[email protected],Cy Young,enterprise,
not-an-email,Di Park,pro,3
[email protected],Ed Fox,gold,2
[email protected],Fay Moss,team,12

The import function validates each row, inserts or updates inside a transaction and commits or rolls back according to a flag. The validation is deliberately plain: an email must look like an email, a plan must be one of free, pro and team, seats must be a whole number of at least one.

function runImport(PDO $pdo, string $path, bool $commit): array {
    $pdo->beginTransaction();
    $summary = ['created' => 0, 'updated' => 0, 'rejected' => 0];
    $report = [];
    $in = fopen($path, 'r');
    fgetcsv($in, 0, ',', '"', '');                       // header
    $line = 1;
    while (($f = fgetcsv($in, 0, ',', '"', '')) !== false) {
        $line++;
        $row = ['email' => trim($f[0]), 'name' => trim($f[1]), 'plan' => trim($f[2]), 'seats' => trim($f[3])];
        $errors = validate($row);
        if ($errors) {
            $summary['rejected']++;
            foreach ($errors as [$col, $val, $msg]) $report[] = [$line, $col, $val, $msg];
            continue;
        }
        // update when the email exists, insert otherwise ...
    }
    $commit ? $pdo->commit() : $pdo->rollBack();
    return [$summary, $report];
}

Run first as a dry run against a table that already holds two of the customers, with the commit flag off:

before: 2 customers
dry run: {"created":1,"updated":2,"rejected":3} -> table still has 2
error report:
line,column,value,problem
4,plan,enterprise,"plan must be one of: free, pro, team"
4,seats,,"seats must be a whole number of 1 or more"
5,email,not-an-email,"not a valid email address"
6,plan,gold,"plan must be one of: free, pro, team"

One customer would be created, two updated and three rejected, and the table still has its two original rows, because the transaction was rolled back. Then the same call with commit on:

commit: {"created":1,"updated":2,"rejected":3} -> table now has 3
  [email protected] | Ada Lovelace | pro | 5
  [email protected] | Bob Stone | free | 1
  [email protected] | Fay Moss | team | 12

The dry run and the commit are the same code path, and the only difference is the last line. That is the design to copy: a preview that runs different code from the real import will drift, and the day it says "all clear" and the real import fails is the day nobody trusts either. Note what the report says. Line 4 has two problems, and both are listed, since a user who is told one error per row will fix, re-upload and meet the second. Lines are physical line numbers in the user's file, with the header as line 1, so they can find the row in a spreadsheet.

Errors the user can act on

An error message is a product feature. "Import failed" is useless, and "Row 4: plan 'enterprise' is not one of free, pro, team" can be fixed in ten seconds. Four rules make the report usable. State the row and the column, in the user's terms: the file's own header (Plan) and not your internal field name. Show the value that failed, so a stray space or a wrong character is visible. Say what would be accepted. And give the whole list in a form the user can work with, which means a CSV of the rejected rows with an added error column, so the user fixes those rows in place and uploads only them (reporting row-level errors back to the user has the format). Cap what you display on screen at the first hundred errors and group them by type, with the full list in the download, because a file with 40,000 bad rows is not one that a person fixes row by row, and the pattern is more useful than the list.

Export is a streaming problem

The first export feature is usually written as: query the rows, build the CSV in a string or an array, return it. It works in testing with 200 rows. At a million rows, it fails. The experiment: produce 1,000,000 rows of four columns (a 40.7 MB file) two ways in PHP, one collecting every row into an array and then writing, one writing each row as it is produced.

array   rows=1000000 time=1.43s peak memory=336.0 MB file=40.7 MB
stream  rows=1000000 time=1.09s peak memory=2.0 MB file=40.7 MB

The array version peaked at 336 MB, and under PHP's default memory_limit of 128 MB it did not finish at all: Fatal error: Allowed memory size of 134217728 bytes exhausted. The streaming version used 2 MB, whatever the row count, and was faster. The same file, two orders of magnitude apart in memory. The principle is general: produce the file row by row, write each row to the response or a file as soon as you have it, and never hold the whole result. In a web framework that means a streamed response with a generator (Laravel's streamDownload, Django's StreamingHttpResponse, Rails' ActionController::Live or a plain enumerator body). Streaming a CSV export from a web app goes through the headers and the buffering traps.

Streaming solves memory. It does not solve time: a request that runs for ninety seconds may hit a proxy timeout, hold a database connection and tie up a worker. For large exports, hand the work to a background job that writes the file and sends the user a link, as in background jobs for large CSV exports. The rule I use: a synchronous download for results the user can see in a few seconds, and a job beyond that, with the threshold set from measurement.

Chunk by key, not by offset

Whether streamed or in a job, a large export reads the table in pieces. The obvious way is LIMIT 10000 OFFSET n, advancing n by 10,000. It gets slower as n grows, because the database has to walk past n rows to find where to start. The alternative is keyset pagination: remember the last key you saw and ask for WHERE id > last ORDER BY id LIMIT 10000. On a million-row SQLite table, in chunks of 10,000:

offset: total 0.75s, first chunk 3.9 ms, last chunk 11.8 ms
keyset: total 0.28s, first chunk 3.2 ms, last chunk 2.7 ms

The offset version took 2.7 times as long overall, and its last chunk cost three times its first. SQLite is a gentle case, since it walks an index cheaply, and in MySQL or PostgreSQL with a sort that is not covered by an index, the penalty is larger. Keyset stays flat. It needs a unique, ordered key, and a result sorted by something else (a name, a date) needs a composite key, such as (date, id). It also has a consistency benefit: rows added during a long export do not shift the pages, so nothing is read twice or skipped.

The contract: names, types and nulls

An export and an import are two halves of a contract, and the contract is in the details. The header row is the interface. Use names a user can recognize, not column names from your schema, and accept variants on import: E-mail, Email address and email_address should all map to the same field. A header-mapping function in PHP, run on three real-looking headers, did it:

["E-mail","Full  Name","Tier","Number of Seats"]  -> mapped {"email":0,"name":1,"plan":2,"seats":3}; missing []; unknown []
["email_address","contact","plan","seats","notes"]   -> mapped {"email":0,"name":1,"plan":2,"seats":3}; missing []; unknown [notes]
["Email","Name","Plan"]                              -> mapped {"email":0,"name":1,"plan":2}; missing [seats]; unknown []

The first header has a byte order mark and a double space, and still mapped. The second has an extra column the importer does not know and reported it as unknown instead of failing. The third is missing a required column and said which. Choosing column names users recognize covers the naming rules.

Types need decisions that a spreadsheet user cannot see. Dates in ISO 8601, so that a file opened in a European Excel and saved again does not flip day and month (handling time zones in CSV timestamps). Booleans as true and false, or 1 and 0, one convention, documented. Money as a decimal with a point and no symbol. Identifiers as text, so that a zip code keeps its zero. And nulls: decide what an empty cell means, and what an empty string means, and whether the two differ.

Round trips, and the lie of identical files

A well-designed export can be fed back into the import and produce the same data, so that a user can export, edit in a spreadsheet and re-import. Test that property, and test it on the data. I exported a table in which one note was NULL and another was an empty string, imported the file into a second table and exported again. The two exports were byte-for-byte identical:

second export identical to first: true
rows that differ after the round trip: 1
  id 1  note before NULL         after ''
  id 2  note before ''           after ''

The files matched and the data did not. The CSV has no way to tell a NULL from an empty string, since fputcsv writes both as nothing, so the import chose empty string and the NULL was gone. Setting the import to read an empty cell as NULL fixed row 1 and broke row 2, where an empty string became NULL. Neither is wrong, and a decision is required: say what an empty cell means in the format's documentation, and if the distinction matters, represent it in the file, with a marker for NULL or by quoting the empty string. A round-trip test that compares files will pass while the data drifts, so compare the tables. Round tripping exports back into imports goes through the other pitfalls.

What happens when the user uploads twice

Users double-click, refresh the page, upload the same file again to be safe and send the corrected file on top of the wrong one. An import feature that has no answer to "this file again" creates duplicates, and the cleanup is a support ticket. Three layers of protection work together. The first is a record of each import: a row in an imports table with the user, the file name, a content hash, the status (previewed, committed, failed), the counts and the times. A commit button that reads "commit import 41" instead of "commit this file" cannot be pressed twice, because the second press finds the import already committed. The second is a business key on the rows, so a repeat updates what is there and does not insert again, which is what the updated count in the dry run shows: two of the six rows matched existing customers by email. The third is the content hash, which lets you say "you uploaded this exact file ten minutes ago" before doing any work, with a button to proceed anyway for the case where it was deliberate (idempotent CSV imports that are safe to run twice goes through the designs).

A related decision is what an import does to rows that are in the application but not in the file. Most features should leave them alone: the file is a set of changes, not a replacement of the whole table. A "replace everything" mode is useful and dangerous, and should be a separate, named option with a confirmation that states how many rows will be removed.

Big imports: transactions, batches and progress

The transaction in the demo wraps six rows. For six thousand, it still works, and for six million it holds locks and a long-lived transaction that other users will feel. The common compromise is to validate the whole file first, without writing, and then commit in batches of a few thousand rows, each its own transaction, in a background job. Batches change the failure semantics: if the job dies at row 3 million, the first 2.9 million are committed. Decide whether that is acceptable and say so on the screen. For an all-or-nothing import, load into a staging table first, validate there, and move the rows into the real tables in one statement (loading a CSV into a staging table, then merging). For an import where partial progress is fine, record which batches completed so that a rerun resumes.

Progress deserves honest treatment. A file of two hundred thousand rows takes minutes, and a spinner with no end is worse than a bar. Count the rows in the inspect stage, update a progress record after each batch, and let the page poll it. Let the user leave the page and come back, with the import visible in a list. And let them cancel: the job checks a flag between batches. Both are small additions and they stop the import screen from becoming the place where people wait and wonder.

Language, locale and the user's spreadsheet

Users edit these files in the locale of their machine, and the file records it. A German Excel writes 1.234,50 and a semicolon, a French one writes dates as 04/03/2026 meaning the fourth of March, a Japanese one may save in Shift JIS if the user chose the wrong format. Accept what you can: detect the delimiter from the first lines (telling what delimiter an unknown CSV file uses), accept UTF-8 with or without a byte order mark and offer an encoding choice for the rest. For numbers and dates, the safest rule is to tell the user the format you expect and show it in the template, with ISO dates and a decimal point, then reject ambiguous values with a message that names the format, since guessing a date order is how a quiet error becomes a wrong invoice (a bank CSV with dates in two formats shows what evidence you can use).

If your interface is translated, the headers of the export should be too, and the importer should accept both the translated and the English names, since a file exported in one language and re-imported by a colleague who uses another is an ordinary event. Keep an internal key for each column, translate only the label, and map on either.

Audit trails and undo

The most valuable feature after the error report is "undo this import". It is cheap if you plan for it: store the import ID on every row the import created or changed, and keep the previous values of the rows it updated. Undo then deletes the created rows and restores the old values of the updated ones, and a user who realizes that the file was the wrong one has a way back that does not involve a support request and a database restore. Even without undo, the import record, with who, when, the file, the counts and the error report, is the audit trail that answers "who changed these customers".

Be careful with what you keep. The uploaded file may hold personal data, and the import record holds a copy of it. Keep it as long as undo or audit needs it and delete it afterward, on a schedule you can state (handling CSV files full of customer data under GDPR).

Permissions are part of the feature

An import writes data, and an export reads it, and both must obey the same rules as the screens. Check three things. Who may import: bulk creation is a power, and many applications reserve it for administrators or a specific role, with the permission checked on the server, never only by hiding the button. Which rows the import may touch: an import that updates by email must be scoped to the importing user's account or tenant, so that a file containing another tenant's email cannot overwrite their record, and the lookup in the update step must include the tenant condition. And which columns: a user who cannot edit a price in the interface must not be able to change it through a column in a file, so the mapping stage should offer only the fields the user may write.

The export has the mirror-image risk. The CSV of a list view must apply the same filters, the same row-level permissions and the same column masking as the list. An export that is built from a separate query by a separate developer is the classic route around access control: the screen hides the salary column and the CSV includes it. Build both from one definition of what the user may see, and test the export as a restricted user. Rate-limit exports as well, since a complete data dump is a one-click operation that deserves a log entry and a limit.

Template files and example rows

The cheapest way to reduce errors is to give users a file to start from. A template download with the exact header row, an example row and, if you can, a second sheet of rules (allowed values, formats) prevents mismatched headers, wrong date formats and the use of the wrong column order. Generate it from the same column definitions that drive validation, so they cannot disagree, and version it: when you add a required column, the template changes, and an old file with the old header should get a message that says which template it came from and what is missing.

Offer the export as the template, too. A user who wants to bulk-update existing records starts from an export of those records, edits the cells and uploads the file. That workflow makes the round trip a first-class feature, which is why the round-trip tests matter: if exporting and re-importing without edits changes anything, the feature teaches users not to trust it.

Measuring the feature

Once it is live, watch it. The numbers worth tracking are small. The share of imports that commit on the first attempt, which says how well the screen and the template guide people. The most common error codes, which tell you where the instructions are unclear or where a validation rule is stricter than users expect. The size of imports and exports, and their duration, which tell you when to move to batches or a job. And the number of support requests that mention a file, which is the metric the rest exist to reduce. A feature that fails for 30 percent of first attempts with the same error is a feature with a defect in its explanation, and the cheapest fix is a better message or a template, not more validation.

Safety

Import and export are attack surfaces, and the checks are the ones in CSV security risks that catch teams out. On export, any text a user typed that starts with =, +, - or @ must be neutralized for spreadsheets (escaping formula characters in a CSV export), because the person who opens the file is not the person who typed the value. On import, enforce limits on size, rows, columns and field length before you parse (limiting file size and row count for CSV uploads), and process the file under a role that can insert only what the user may create. Permissions and tenancy belong in the commit stage: an import into one customer's account must never touch another's rows, and an export must apply the same row-level filters as the screen it came from, or it becomes the way around them. Record who imported what and when, since a bulk import is the easiest way to change a thousand records at once and the first thing someone asks about afterward.

Working with Excel users

Most people who touch these files use Excel, and a feature that ignores that gets support tickets. Offer an Excel-friendly export, UTF-8 with a byte order mark so accents survive a double-click, and say so on the button (Excel CSV UTF-8 versus CSV comma delimited). Accept both comma and semicolon delimiters on import, since a European Excel writes semicolons (opening a semicolon separated CSV in Excel). Offer a template download with the exact headers and an example row, which prevents more errors than any validation. And assume that identifiers will be mangled if a person edits the file in a spreadsheet: leading zeros and long numbers are the usual casualties (opening CSV files in Excel and Sheets without damage), so validate them on import and say what went wrong.

Testing the feature

The tests that earn their keep are few. A fixture of awkward files: with a byte order mark, with semicolons, with quoted newlines, with every error type, empty, header only, and one that is too large. A dry-run test that asserts the table is unchanged afterward. A commit test that asserts the counts. A round-trip test that compares tables. An error-report test that asserts the line numbers refer to the physical lines of the uploaded file. And a large-file test that exports a million rows and asserts that memory stays below a figure, since a streaming export that someone later turns into an array will pass every small test and fail in production.

When CSV is the wrong interface for the job

CSV import suits bulk loading of records people already have in a spreadsheet and occasional bulk edits. It is the wrong interface when the data comes from another system on a schedule: an API or a managed connector gives you typed fields, authentication, retries and no dialect problems. It is the wrong interface for editing a few records, where a form is better, and for data with relationships, orders with line items, where a flat file cannot express the structure without conventions that are fragile. And it is the wrong export when the consumer is a program: JSON or an API preserves types and nesting, and a CSV makes the consumer guess.

Where it is right, build it as described: staged, previewed, streamed, tested on round trips and defended. The framework-specific tools, queues, streamed responses and validators, differ, and the shape does not. The format is defined in RFC 4180, the PHP functions in the fgetcsv documentation, and the upload controls in OWASP's file upload cheat sheet.