Import and Export Features: Peaceful Coexistence Between Your System and Excel

People who build systems share a common naive belief: "Once the system launches, everyone will stop using Excel." We can tell you honestly: they won't. Ten years after launch, users will still ask "can this list be exported to Excel?" Rather than fighting it, build import/export well — it's the bridge that lets the system and Excel coexist peacefully, and an invisible feature that decides whether adoption succeeds.

Why users will always need Excel

Three very practical reasons. First, the report format the boss wants changes forever — no amount of customization keeps up with "add one more column this time," and exporting to build a pivot table yourself is fastest. Second, exchanging data with outside partners — a supplier's quote sheet, a logistics provider's statement — arrives as Excel, and you can't demand the whole world integrate via API. Third, initial data entry: a company with thousands of products or members switching systems cannot ask staff to key them in one by one.

So when we plan any system, import/export isn't a "value-add for later" — it's infrastructure that ships in version one. It's the same idea we raised in when to move from Excel to a system: switching systems isn't about eliminating Excel; it's about moving the single source of truth into the system, with Excel retreating to the role of a tool.

The three lines of defense for imports

First line: template design

Never let users freestyle the columns — always provide a downloadable standard template. The details matter: column headers in plain language rather than database field names, required fields clearly marked, and a sample row in the template demonstrating date format. The rake we stepped on was dates — Excel helpfully converts "2025-01-05" into its own date serial number, and different machines' regional settings produce different results. Our fix: set date columns in the template to text format outright, and tolerate several common date spellings at import time.

Second line: validate first, write second

The worst import experience: upload a thousand rows, the system hits an error at row three hundred and stops — the first three hundred are in, the remaining seven hundred aren't, and the data is now half-cooked. The right approach is two-phase: validate the entire batch first and list every error at once; only when everything passes does the write happen, and if any row fails, the whole batch stays out. Technically this is a transaction; to the user it means "all succeed or all fail" — never an in-between state.

Third line: error reports in human language

"Row 47 failed to import" is useless — the user opens the file and has to guess what's wrong. A helpful message looks like: "Row 47: product code A102 already exists — use the update import to modify it" or "Row 89: price field says 'negotiable' — must be a number." Better still, have the system generate an annotated copy of the Excel file for download, with the reason written next to each failing cell — fix and re-upload.

The quality of an import feature is determined by how it handles bad data, not how it handles good data.

Two practical details on exports

Export looks simple but has two traps. One is encoding: export a CSV for Excel on Windows without handling encoding and you get a screen full of mojibake — a problem that's been recurring for twenty years. Exporting xlsx directly is the easiest way out. The other is volume: when a user clicks "export all," it might be a hundred thousand rows, and processing synchronously spins the page until it times out. Large exports should generate the file in the background and notify the user when it's ready — don't make them stare at a progress bar and gamble.

Don't forget permissions

One final, often-overlooked point: export means data leaving the system. Member lists, cost prices, revenue details — once exported to a file, they're outside the system's permission protections. Which roles can export which data, and whether exports get logged, must be decided at planning time. Our practice: every export writes to the audit log — time, person, scope. When something goes wrong, that log is the only thread to pull.

If you're evaluating moving your Excel-based operations into a system, the quality of a vendor's import/export design is an excellent test question. See our custom systems and SaaS services.