Storing Uploaded Excel Files and Their Parsed Rows

Users upload spreadsheets. They always have, and the files are never quite what the template said: an extra header row, dates as text in one row and numbers in the next, a blank line in the middle, a sheet renamed. An import that works on the happy path and fails opaquely on the rest generates support tickets. The design that holds up keeps the original file, parses into a staging area, validates with SQL and reports errors by row. This article lays out that design for PostgreSQL, from the upload record to the final tables, with the handling of the spreadsheet traps.

Keep the original

Record every upload before doing anything else:

CREATE TABLE upload (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uploaded_by bigint NOT NULL,
    uploaded_at timestamptz NOT NULL DEFAULT now(),
    filename    text NOT NULL,
    size_bytes  bigint NOT NULL,
    sha256      text NOT NULL,
    storage_key text NOT NULL,
    status      text NOT NULL DEFAULT 'received',
    row_count   integer,
    error_count integer,
    UNIQUE (uploaded_by, sha256)
);

The file itself goes to object storage with the key in the row, or into a bytea column if files are small and you want one backup, as weighed in large objects versus files on disk. The original is your evidence: when a customer says "I uploaded 500 rows and only 480 arrived", you can reparse the same bytes. The hash catches the same file uploaded twice; the unique constraint turns a duplicate into a message ("you already uploaded this file on March 4") instead of double-counted data. Keep the original name only as metadata, and generate your own storage key.

Parse in the application

PostgreSQL cannot read .xlsx files, which are zip archives of XML. Parse in application code with a library: openpyxl or pandas in Python, exceljs or xlsx in Node.js, Apache POI in Java, ClosedXML in .NET. Open the workbook in read-only mode to stream large sheets without loading everything. Read values, not formulas (data_only=True in openpyxl returns the cached results), and be aware that cached values exist only if the file was last saved by Excel; a file written by another tool may have formulas without results. Choose the sheet by name, not index, and fail with a clear message if it is missing. Detect the header row by looking for the expected column names rather than assuming row 1, and map columns by header text, case-insensitively, so a reordered column does not corrupt the data.

Stage the rows

Write every row into a staging table with text columns, the upload id and the position:

CREATE TABLE stage_order_row (
    upload_id   bigint NOT NULL REFERENCES upload(id),
    sheet       text NOT NULL,
    row_no      integer NOT NULL,
    order_ref   text,
    customer    text,
    quantity    text,
    ordered_on  text,
    PRIMARY KEY (upload_id, sheet, row_no)
);

Load with COPY FROM STDIN from the parser's output (see using COPY with STDIN from an application), which is much faster than row inserts for thousands of rows. Staging as text means the load cannot fail on a bad value; the structure of the data is checked in SQL next. The row_no is the spreadsheet row number as the user sees it (1-based, counting the header), so error messages can say "row 17". Skip fully blank rows in the parser, and trim trailing empty columns.

Validate with SQL

Validation is a set of queries that insert problems into an errors table:

CREATE TABLE import_error (
    upload_id bigint NOT NULL, sheet text, row_no integer, column_name text, message text
);

INSERT INTO import_error (upload_id, sheet, row_no, column_name, message)
SELECT upload_id, sheet, row_no, 'quantity', 'not a whole number: ' || quantity
FROM stage_order_row WHERE upload_id = $1 AND quantity !~ '^[0-9]+$';

INSERT INTO import_error (upload_id, sheet, row_no, column_name, message)
SELECT s.upload_id, s.sheet, s.row_no, 'customer', 'unknown customer: ' || s.customer
FROM stage_order_row s LEFT JOIN customer c ON c.code = s.customer
WHERE s.upload_id = $1 AND c.id IS NULL;

Add checks for required fields, ranges, duplicates within the file (GROUP BY order_ref HAVING count(*) > 1) and duplicates against existing data. The user then sees all the problems at once, not the first one, with row and column. Decide the policy: reject the whole file if any error exists (simple and predictable), or import the good rows and report the bad ones (friendlier, with the risk of partial data that the user must fix and re-upload carefully). Whichever you choose, make it explicit in the interface and in the status column.

Load the final tables

When validation passes, convert and insert in one transaction:

INSERT INTO sales_order (order_ref, customer_id, quantity, ordered_on, source_upload_id)
SELECT s.order_ref, c.id, s.quantity::int, s.ordered_on::date, s.upload_id
FROM stage_order_row s JOIN customer c ON c.code = s.customer
WHERE s.upload_id = $1
ON CONFLICT (order_ref) DO UPDATE SET quantity = EXCLUDED.quantity, ordered_on = EXCLUDED.ordered_on;

The source_upload_id links every final row to the upload that produced it, so you can find, review or undo an import: DELETE FROM sales_order WHERE source_upload_id = $1 reverses a bad upload (when the import only inserted). Mark the upload imported with counts in the same transaction. The staging rows can be kept for a retention period for debugging and then purged, following time based data retention jobs.

Spreadsheet traps

Excel files hold surprises. Dates are numbers: Excel stores a date as days since 1899-12-30 (or 1904 for some files), and a library may return a datetime or a float depending on cell formatting, so handle both. A text date like 03/04/2026 is ambiguous and should be refused or parsed with a declared format. Numbers formatted as text keep leading zeros in postal codes and account numbers, which must stay text. Merged cells return a value only in the top-left cell and empty elsewhere. Hidden rows and sheets are still read unless you skip them. Percentages are stored as fractions. Large numbers may be stored as floats and lose precision beyond 15 digits. Formulas can return errors such as #DIV/0! as strings. Rich text and cell comments are not data. Macros in .xlsm files must never be run, and files from untrusted users deserve a size limit, a type check and a parser that does not evaluate anything. .xls and .csv files need other parsers.

Performance and limits

A sheet of 100,000 rows parses in seconds in read-only mode and stages in seconds with COPY. Memory is the usual limit: non-streaming reads of big workbooks use hundreds of megabytes. Enforce a maximum file size and row count at upload, and process large files in a background job, showing progress to the user through the status column. Run each upload's validation in its own transaction with a statement timeout. Index staging on (upload_id) only as needed, since the table is written once and read a few times; partition or purge it regularly.

When the original file does not need to be kept

If the uploads are throwaway, a one-time migration or a feed from your own systems, the original adds storage and privacy exposure without benefit. Keep only the hash, the metadata and the parsed rows, or only the audit trail of who imported what. If uploads contain personal data, retention rules apply to the original as much as to the rows, and deleting the file when the import is complete may be the right policy. Keep the original when users dispute results, when audits need the source, when you may reparse after fixing a bug, or when the file is the contractually relevant document.

What to do next

Create the upload table, write the parser that fills the staging table with row numbers, and add three validation queries. Test with a file with a blank row, a text date and a duplicate. The libraries are documented at openpyxl and the PostgreSQL manual covers COPY in COPY. The hub is reading and writing files from PostgreSQL.