The customer uploads a spreadsheet and the import dies halfway: designing bulk CSV and Excel import

Bulk import is not one operation. It is three that need to stay separate: a dry run that reads the file, applies every rule and writes nothing, a write job that produces the same result if you run it twice, and a report that says row by row what happened. Almost every import that dies halfway is doing all three inside a single HTTP request, straight against live tables. The second thing worth knowing up front is that the file a user uploads is not data. It is a snapshot of how that user understood your data model. The real job of an import feature is not moving rows, it is showing the gap between those two understandings in language the user can act on.

Why a single-request import dies halfway

The scenario is the same everywhere. The user uploads an 18,000 row file, a spinner turns in the browser, and thirty seconds later the gateway times out. How many rows made it into the database? Nobody knows. What does the user do next? Upload the file again.

The correct flow is duller. The upload request puts the file in object storage, creates an import record, queues a job and returns. On the HTTP side that is 202 Accepted in RFC 9110: the request has been accepted for processing but the processing is not complete, and the response either carries the status or points at something that tracks it. The user watches progress from there, or closes the tab and comes back. The retry and poison message rules from background jobs and queues apply here without modification.

One rule on the reading side: never load the whole file into memory. Use a streaming parser that walks rows. A 50 MB xlsx can comfortably expand past a gigabyte in memory, and one user's upload takes the whole server down with it.

Excel may have changed the data before it ever reached you

Most import bugs are not in your code. They happen the moment the user saves the file. There is a measured example of this in the scientific literature. A 2021 PLOS Computational Biology study that scanned supplementary files from genetics papers found gene name errors in 30.9% of publications containing Excel gene lists between 2014 and 2020. The cause was mundane: Excel silently converted symbols like SEPT1 and MARCH1 into dates. It got far enough that the gene nomenclature committee renamed the genes to fit the software. SEPT1 is now SEPTIN1.

Here is what the same class of problem looks like in your files:

• Leading zeros disappear. Tax numbers, postal codes, bank account numbers and barcodes that start with zero lose it the moment the cell is treated as a number.
• Long numbers get mangled. Excel's own published limits put number precision at 15 digits. The sixteenth digit of a card or reference number becomes a zero, with no warning anywhere.
• Dates are serial numbers. There are no dates inside an xlsx, only numbers. Excel uses the 1900 date system and, as Microsoft documents openly, treats 1900 as a leap year even though it was not. The behaviour was kept deliberately for Lotus 1-2-3 serial date compatibility. On top of that, a workbook can be set to the 1904 date system instead. Do not assume which epoch a serial number is based on, read it from the file.
• Separators and decimal marks follow the locale. Microsoft's text file import guidance says the list separator comes from Windows Region settings. Anywhere the decimal mark is a comma, which covers most of Europe, the list separator becomes a semicolon. So the file your user calls a CSV has semicolon-separated fields and prices written as 1.250,75.
• Encoding is guesswork. Double-click a UTF-8 CSV with no byte order mark and Excel on Windows interprets it with the system code page, which is how accented and non-Latin characters turn into noise. The "CSV UTF-8" save option added in Excel 2016 fixes it, and most users never pick that line in the dropdown.

The practical consequence: do not expect users to save files correctly. Accept both semicolons and commas, detect the encoding and strip the BOM, read identifier columns as text even when they look numeric, and state the expected date format in the template. A downloadable sample template prevents more errors than any validation rule you will write.

RFC 4180 defines CSV: records are separated by CRLF, fields containing commas, quotes or line breaks are enclosed in double quotes, and a double quote inside a field is escaped by doubling it. What matters is that the document is informational rather than a standard, and that a good share of real world files ignore it. Do not write your own parser. Use a mature library and give it the dialect hints it needs.

Dry run first, write second

The first thing a user should see on the import screen is not a result, it is a summary. The file is read, every rule runs, nothing is written, and the screen says: 1,418 rows read, 1,102 records will be created, 281 updated, 35 rejected. Underneath it, a downloadable list of the rejected rows.

How that list is designed decides whether the feature survives contact with real users. Three rules:

1. Row numbers must match the user's file. Numbers that shift because you counted the header or skipped blank rows send people to the wrong line. Include the column name too.
2. The message has to state the rule. "Invalid value" is not an error message. "Tax number must be 10 or 11 digits, received 9" is. The same principles from designing user-facing errors apply directly.
3. The output has to be fixable and re-uploadable. The best error report is a file containing only the failed rows, in the original layout, that the user can correct and send straight back.

Validate in two layers. Row level checks look at the field: required, correct type, length and format. Set level checks look at the file as a whole: is the same key present twice, does the referenced customer or product exist, do the totals match the number the user expects. Skip the second layer and you get files where every row is individually valid and the file as a whole is nonsense.

All or nothing, or row by row

This is a business decision, not a technical preference. For data whose integrity is meaningful as a unit, like a journal entry, a stock count or a price list, a half-finished import is the worst outcome: one transaction, and if anything fails nothing is written. For data where rows are independent, like a contact list or product descriptions, rejecting 1,383 good rows because of 35 bad ones just wastes the user's afternoon.

Do not ask the user to choose. Decide per data type, and say so on the screen. If you do not, the user invents an assumption, and it is usually the wrong one.

Even in row-level mode, do not write one row at a time. Process in chunks, commit each chunk in its own transaction, and record progress against the import record. If the job dies at row 9,400 you should be able to resume knowing exactly where it stopped.

What happens when the same file is uploaded twice

It will be. Because the user could not see the result, because the browser froze, because two colleagues both had the file. Answer the question before it is asked.

Three mechanisms cover it. First, every row needs a business key, and the write has to be an upsert rather than an insert. Finding that key is the customer's job as much as yours: require an external ID column in the file and reject imports that leave it blank. Importing without a key is the fastest way to manufacture duplicate records.

Second, store a hash of the uploaded file. If the same hash arrives from the same account within 24 hours, ask: "You uploaded this file today at 14:20 and 1,418 rows were processed. Run it again?"

Third, idempotency at the request level for integrations that trigger imports over the API. The Idempotency-Key header is still an expired IETF draft rather than an RFC, but it has become the de facto standard anyway. Stripe's implementation is a reasonable reference: the client generates the key, the result of the first request is saved and replayed, reusing a key with different parameters returns an error, and keys can be pruned after 24 hours.

Two things to get right on the database side

First, land the raw data in a staging table before touching anything real. Every column as text, plus the source row number and the import ID. Validating and transforming there in SQL is faster and far more auditable than running application code per row, and your error report becomes a query. PostgreSQL's bulk loading guidance backs the approach: loading a large number of rows with COPY is almost always faster than INSERT, even with prepared statements and batched transactions. The same page is worth reading for the rest of it, including the reminder to run ANALYZE afterwards, since the planner's statistics go stale the moment you load a few hundred thousand rows.

Second, and more often forgotten: an import fires every per-row side effect you have. Update 1,400 records and you can generate 1,400 "your record changed" emails, 1,400 webhook deliveries and 1,400 search index writes. Sending a customer thousands of notifications in one minute is a worse incident than the import failing. Either suppress notifications in import context or collapse them into one digest. The batching logic in notification system design exists for exactly this case.

Both the uploaded file and the downloaded one are security surface

On the way in, an xlsx is a zip archive full of XML, which means zip bombs and XML entity expansion come through this door. That is not theoretical. The openpyxl documentation states plainly that it does not guard against quadratic blowup or billion laughs XML attacks by default and that you should install defusedxml to do so. Add a file size and row count ceiling, content-based type detection rather than trusting the extension, and parsing inside a worker with minimal privileges. The general rules from file upload handling all apply, plus one extra: the uploaded file is itself customer data, so it needs a retention period rather than sitting in a bucket forever.

On the way out there is a hole pointing the other direction: CSV injection. Per OWASP, cells beginning with =, +, -, @ or with tab, carriage return and line feed characters are treated as formulas by spreadsheet software. Export a field a user typed and that formula runs on somebody else's machine. Keep the same page's honest caveat in mind: there is no universal CSV sanitization strategy that is safe for all spreadsheet applications. Escape those characters on export, serve downloads with Content-Disposition: attachment, and prefer generating xlsx where you can.

Import is a product feature, not a one-time task

Most teams build import as "let's get the data in once," and then the screen runs for years. The price list comes monthly, stock weekly, a new branch's data every quarter. Accept that at the start and three things follow naturally.

Every import gets a record: who, when, which file, rows read, rows written, rows rejected, how long it took. Every written row carries that import ID. And rollback becomes possible, because "undo the 1,102 records loaded yesterday" is a question that eventually gets asked, and without an import ID there is no answer to it. That record is part of your audit trail anyway, and an audit log that does not cover bulk changes is missing the largest changes in the system.

Save column mapping templates too. When the same supplier sends the same headers every month, mapping once and reusing it turns a ten minute chore into a ten second one. If what you are actually planning is a one-time move off an old system, the rules differ somewhat, and migrating data from a legacy system covers that side.

A first step that fits in this week

Ask for the files from the last three failed imports and open them. Before looking for the bug in your code, look for it in the file: which separator, which encoding, what date format, did the leading zero survive in the ID column. Most teams find in that five minute exercise that two thirds of their failures come from the same two causes.

Then build one thing: a dry run mode that writes nothing. It reads the file, runs the rules, produces the "x new, y updated, z rejected" report, and makes the rejected rows downloadable with their original row numbers. Even without touching the write path, that single screen ends most of the support calls your import feature generates. If imports keep stalling and you are not sure which end to start from, we can walk through your current flow and work out the order to add these three steps.