Data export and import are the features nobody gets excited about building but everybody gets frustrated about when they do not work. A CFO who cannot get last quarter’s transactions into a spreadsheet for board reporting will not care how elegant your API is. A customer success team that cannot bulk-import 10,000 contact records from a CSV will abandon your product for one that lets them. These are table-stakes features for any serious business application, yet they are riddled with subtle engineering challenges: character encoding, date format ambiguity, memory management for large files, error handling granularity, and the ever-present question of how much processing to do synchronously versus asynchronously.
This post covers the design and engineering of data export and import features that handle real-world data — messy, large, and demanding.
Export Architecture: Streaming, Not Buffering
The naive approach to CSV export is to load all matching records into memory, serialize them, and send the result as a single response. This works for 100 rows. At 100,000 rows, your application server runs out of memory, times out, or both.
The correct approach is streaming. In a streaming export, the application opens a database cursor that fetches rows in batches (1,000 at a time is a reasonable default), serializes each batch to CSV, and writes it to the HTTP response as a chunked transfer-encoded stream. The client begins downloading immediately, and the server’s memory footprint stays constant regardless of dataset size.
In Node.js, this looks like piping a readable stream (from the database cursor) through a transform stream (CSV serialization) to the response writable stream. In Python with Django or Flask, generator-based responses achieve the same effect. In Ruby on Rails, ActionController::Live or the send_data approach with an Enumerator provides streaming.
For Excel exports (XLSX format), streaming is harder because the XLSX format requires random access to write sheet dimensions and shared string tables. The streaming-xlsx-writer pattern works around this by writing a temporary file on disk using a library like openpyxl in write-only mode or exceljs in streaming mode, then sending that file. The temporary file should be written to a fast local disk (or tmpfs in Linux) and deleted after transmission.
Important detail: Set the Content-Disposition header to attachment; filename="export-2025-05-12.csv" to trigger a download rather than inline display. Include a meaningful filename with the date and a description of the data (e.g., orders-2025-Q1.csv).
CSV Gotchas That Will Bite You
CSV is “simple” the way English is “simple” — everyone thinks they understand it until edge cases appear.
Encoding: Always export as UTF-8 with a byte-order mark (BOM: \xEF\xBB\xBF). Without the BOM, Excel on Windows defaults to the system’s locale encoding (often Windows-1252), which mangles non-ASCII characters. The BOM is invisible to most other tools and solves the single most common CSV-in-Excel complaint.
Delimiter ambiguity: Some locales (Germany, France, Brazil) use a comma as the decimal separator, which means Excel in those locales interprets semicolons as the CSV delimiter. Two strategies: (1) offer a delimiter choice in the export dialog, or (2) include a sep=, directive as the first line of the CSV, which tells Excel to use a comma regardless of locale.
Date formatting: Export dates in ISO 8601 format (2025-05-12T14:30:00Z). It is unambiguous and parseable by every tool. Never export dates in locale-specific formats like 05/12/2025 because it is unclear whether that is May 12 or December 5. If your users specifically need locale-formatted dates for presentation, offer it as an export option alongside a machine-readable format.
Newlines in cell values: If a field contains a newline character, the entire field must be enclosed in double quotes. If it contains a double quote, the quote must be escaped by doubling it (""). Use a proper CSV library rather than string concatenation — hand-rolled CSV serialization is the number-one source of export bugs.
Numeric precision: JavaScript’s Number.toString() can produce scientific notation for very large or very small numbers (1e-7). Excel will not interpret this as a number. Format numbers explicitly with fixed decimal precision.
Import: Where the Real Complexity Lives
Export is a controlled operation — you know your data model, your encoding, your formats. Import is the opposite: you are accepting data shaped by whatever tool the user used to create it, with whatever encoding, whatever date format, and whatever creative interpretations of your expected schema they produced.
Step 1: File validation. Before parsing, check file size (reject files above your limit — 50MB is reasonable for CSV), MIME type (though MIME detection is unreliable for CSV), and encoding. Use a library like chardet (Python) or jschardet (JavaScript) to detect encoding and transcode to UTF-8 before parsing.
Step 2: Header mapping. The user’s CSV columns will not match your field names exactly. “First Name,” “first_name,” “FirstName,” “fname,” and “given name” all mean the same thing. Provide an interactive column-mapping step where the user maps their columns to your fields. Pre-populate mappings using fuzzy string matching (Levenshtein distance or simple normalization: lowercase, strip whitespace and punctuation, compare). This mapping step is the most important UX decision in the entire import flow.
Step 3: Validation. Parse each row against your field-level validation rules: required fields, data types, value ranges, referential integrity (does this referenced record actually exist?), uniqueness constraints. Collect all errors, do not stop at the first one. Return a validation report that shows the user exactly which rows have which problems:
Row 12: "email" is not a valid email address ("john@")
Row 45: "department_id" references a department that does not exist ("DEPT-999")
Row 45: "start_date" could not be parsed as a date ("next Monday")
Row 78: duplicate "employee_id" — conflicts with row 23
Step 4: Preview and confirmation. Show the user a preview of what will be imported: total rows, rows that will succeed, rows with errors. Let them download just the error rows as a separate CSV for correction. Allow them to proceed with partial import (skip error rows) or cancel and fix.
Step 5: Execution. The actual database writes should happen in a background job, not in the HTTP request. For large imports, batch the inserts (500-1000 rows per batch), wrap each batch in a transaction, and update a progress indicator via WebSocket or polling. If a batch fails, roll back that batch and continue with the next, recording which rows failed.
See also: Building White-Label SaaS Platforms for Multiple Brands
Handling Large Files Without Killing Your Server
A 100MB CSV with a million rows is not unusual in business contexts. Processing it requires attention to memory and time.
Parse incrementally. Use a streaming CSV parser that emits rows one at a time rather than loading the entire file into memory. papaparse (JavaScript) and Python’s built-in csv module both support streaming. Read from a file on disk, not from a buffer in memory.
Validate in a background job. Upload the file, store it (on disk or in object storage like S3), and return immediately. A background worker picks up the file, parses it, runs validation, and stores the validation report. The user polls for status or receives a notification when validation is complete.
Use COPY for bulk inserts. PostgreSQL’s COPY command ingests CSV data at roughly 100,000 rows per second, orders of magnitude faster than individual INSERT statements. Format your validated, transformed data as a CSV stream and pipe it directly to COPY. For databases that lack a COPY equivalent, use multi-row INSERT statements (batches of 500-1000 rows) within transactions.
Provide progress feedback. For a million-row import, the user needs to know the job is progressing. Track rows processed, rows succeeded, rows failed, and estimated time remaining. Update a progress record in the database every 1,000 rows. The frontend polls this record (or subscribes via WebSocket) and displays a progress bar.
Export Formats Beyond CSV
CSV is universal, but it is not always sufficient. Business applications should support multiple export formats, each serving a different use case:
XLSX (Excel): When the export is destined for manual review, formatting matters. XLSX supports column widths, number formatting, date formatting, header styling, and multiple sheets. Use it for reports — a “Summary” sheet with aggregates and a “Detail” sheet with row-level data. AutoFilter headers (the dropdown arrows on column headers) are a small touch that users deeply appreciate.
PDF: For reports that will be printed, shared externally, or archived. Generate PDFs server-side using a headless browser (Puppeteer rendering an HTML report) or a dedicated library (PDFKit for Node.js, ReportLab for Python). Include page numbers, a generation timestamp, and the filters/parameters used to generate the report.
JSON and JSON Lines: For developer-oriented consumers and API-driven workflows. JSON Lines (one JSON object per line, newline-delimited) is preferable to a single JSON array for large exports because it supports streaming and incremental processing.
Parquet and other columnar formats: For data engineering consumers who will load the export into a data warehouse. Parquet files are compressed, column-oriented, and carry schema information. This is an advanced feature but a meaningful differentiator for data-heavy B2B applications.
Access Control and Audit Logging
Export and import features handle bulk data, which makes them high-value targets for data exfiltration and high-risk operations for data corruption.
Export permissions should be granular. A user who can view a single customer record should not necessarily be able to export all 50,000 customer records. Implement export-specific permissions and consider row-level limits: “users in the Support role can export up to 1,000 records per export.”
Audit logging for every export and import operation should record who performed it, when, what filters were applied (for exports), how many records were affected, and the file’s hash. This audit trail is essential for compliance frameworks (SOC 2, GDPR, HIPAA) and for incident investigation.
Rate limiting on export endpoints prevents abuse. A reasonable limit is 10 exports per user per hour, with higher limits for admin roles.
Data minimization: Export only the fields the user has permission to see. If a user lacks access to salary data, the export should not include salary columns — even if the underlying query could include them.
Data export and import might not be glamorous, but they are the features that determine whether your application fits into your customers’ real workflows or remains an island. If you need these capabilities built right — robust, scalable, and tested against the messy reality of production data — reach out to us.