CSV Imports: The Files Users Hand You and the Database You Have to Protect

Every SaaS that ships an export eventually gets asked for the import. It sounds like the same feature pointed the other way, and it isn't: an export only has to be correct on your side, while an import has to survive data you don't control. The file was edited in two different spreadsheet programs, the email column contains a city name, and the third sheet of the workbook is the one the user actually meant. A good import either finishes clean or tells the user exactly which row is wrong and why. What it should never do is half-apply.

Parse everything first, write nothing

The dry run is the real feature. Phase one parses the whole file and validates every row against the rules the database will enforce. Phase two writes. If validation and writing interleave, the run fails at row 500 with 499 rows already inserted, and the user's next attempt imports those 499 again. Separating the phases removes that entire class of bug.

In Laravel the shape is one job that reads rows into a validated collection (or a temp file on disk for large ones), runs each row through a FormRequest or a validator, and collects problems:

$rowErrors[] = [
    'row'    => $rowNumber,
    'column' => 'email',
    'value'  => $row['email'],
    'error'  => 'not a valid email address',
];

If the error list is empty, the apply step runs. If it isn't, the apply step never starts, and the user gets a report instead of a surprise.

Errors talk in row numbers, not in apologies

"Invalid file" is the message that turns a two-minute fix into a support thread. Every validation failure should name the row, the column, and the value it saw. Report all of them, not just the first one; a user with 30 bad emails wants to fix them in one pass, not thirty uploads.

The most common causes, from the files we've processed:

Encoding that isn't UTF-8. Excel on Windows still saves CSVs in legacy codepages, and the file arrives as cp1252 (or worse, since people keep editing them in different countries). Detect, convert, and say so in the report.
Locale-dependent numbers. 1,234.56 from one user and 1.234,56 from another are the same amount, and naive parsing turns both into garbage. Imports of money get parsed per the rules of the file's locale, stated in one place, never guessed from the user's profile. (Our piece on storing money explains why this matters downstream.)
Column order changed to "make it look nicer". Match columns by header name, not by position, and refuse to start when a required header is missing or misspelled. Silently shifting data across columns is the worst failure an import can have, because it produces well-formed wrong rows.

Duplicates: in the file, and already in the database

Two layers, both explicit. First, within the file: the same invoice number twice is either a user mistake worth rejecting or a legitimate update, and the app should have an opinion instead of inserting both. Second, against the database: for each row, decide skip, update, or error, and make that decision visible in the import screen before anyone clicks Run.

Whatever the decision, the database constraint is the enforcement, not the check. A UNIQUE index (or a partial index that ignores soft-deleted rows) is what keeps a concurrent import or a manually re-run file from double-inserting, the same principle our race conditions article applies to payments. The application check is a nicer error message; the constraint is the guarantee.

Apply in chunks, report honestly

The write phase inserts in batches, inside one transaction for files of reasonable size, or as a queued job with a per-batch commit for large ones. An import ID ties the rows together, so a re-run of the same file is either a clean upsert or a no-op rather than a duplicate job.

The finishing screen matters as much as the parsing: rows imported, rows skipped, rows rejected, and a downloadable error file carrying the same row, column, value, and reason. That file is what the user forwards to the colleague who owns the spreadsheet, and it's why the next attempt usually succeeds without a ticket.

None of this is heavy. A dry run, row-level errors, header matching, a real unique constraint, and a plain report are a day or two of work, and they're the difference between an import people trust and one they dread.