Staging Excel Data in a Table Before Cleaning It Up

The instruction "clean the data and load it" hides two jobs: getting the data into the database, and making it fit. Doing both in one step means a bad cell stops the whole load with an error that names no cell. Separating them with a staging table gives you a place where the raw sheet sits, in SQL, where you can query it, count it and argue about it before anything reaches a table that other people use. This post covers the design of that table, how to load it fast and the queries that find the problems. It belongs to the cluster on getting Excel data into a database.

What a staging table is

A staging table is a table with one purpose: to hold a sheet's rows in a form that cannot fail. Every data column is text. No NOT NULL on the data, no primary key on the data, no foreign keys, no check constraints. Anything the sheet contains can be inserted, including a column of dates, numbers and the word unknown in the same field.

Next to the data columns it carries provenance:

CREATE TABLE stage_orders (
    batch_id     bigint  NOT NULL,
    source_file  text    NOT NULL,
    source_sheet text    NOT NULL,
    source_row   integer NOT NULL,
    order_no     text,
    customer     text,
    amount_eur   text,
    ordered      text
);

The provenance columns are the reason a staging table beats a direct load. When a total in a report is wrong, the question is which cell is responsible. With batch_id, source_file, source_sheet and source_row, a bad row maps to orders.xlsx, sheet Orders, row 17, and you can open the workbook at that cell. Without them, you diff the sheet against the table by eye.

Name the data columns after the target columns, not after the sheet's headers. The sheet's Amount (EUR) becomes amount_eur. The mapping from header to column belongs to the loader, and a header that changes then fails the load with a clear message ("expected column Amount (EUR)").

One table per sheet, per source

Make one staging table for each distinct sheet layout. If three workbooks each have an Orders sheet in the same layout, they share stage_orders, distinguished by batch_id. If a vendor sends a different layout, give it its own stage. Do not widen one table until it has the union of every column ever seen, because the profiling queries then drown in null columns.

Temporary tables disappear at the end of the session, which is tempting, and wrong when you want to keep the evidence. Use permanent tables in a separate schema, staging, with permissions that let the loader write and only analysts and the loader read.

Load it quickly

Row-by-row inserts are the slow way. Use the bulk path of your engine:

On PostgreSQL, a staging table that you can recreate at will can be UNLOGGED, which skips the write-ahead log and makes bulk writes faster, with the trade-off that its contents are lost after a crash:

CREATE UNLOGGED TABLE staging.stage_orders (LIKE stage_orders INCLUDING ALL);

For a stage this is usually acceptable, since the data can be reloaded from the workbook. It is not acceptable if the stage is your only record of an import. Wrap a load in one transaction so that a failed load leaves nothing behind, and use a unique batch_id so that repeated attempts do not interleave.

Do not create indexes on the stage before the load; build them afterward if the profiling queries need them. For tables of a few hundred thousand rows, you will not need any.

Profile before you convert

Once the rows are in, a handful of queries describes the data. Start with counts:

SELECT count(*) AS rows,
       count(*) FILTER (WHERE order_no IS NULL OR order_no = '') AS blank_order_no,
       count(DISTINCT order_no) AS distinct_order_no
FROM stage_orders
WHERE batch_id = 42;

 rows | blank_order_no | distinct_order_no
------+----------------+-------------------
  845 |              3 |               840

That one result already contains findings. Three rows have no order number, and 845 rows carry 840 distinct numbers among the non-blank ones (the three blanks collapse into no group in count(DISTINCT), so with 842 non-blank rows and 840 distinct values, at least two rows repeat a number). Look at the duplicates:

SELECT order_no, count(*) AS n, array_agg(source_row ORDER BY source_row) AS rows
FROM stage_orders
WHERE batch_id = 42 AND order_no <> ''
GROUP BY order_no
HAVING count(*) > 1;

The array_agg of source_row points at the exact rows to inspect in the workbook. Then look at patterns. Group by a generalized shape of each value to see formats at a glance:

SELECT regexp_replace(regexp_replace(ordered, '[0-9]', '9', 'g'), '[A-Za-z]', 'a', 'g') AS shape,
       count(*) AS n
FROM stage_orders
WHERE batch_id = 42
GROUP BY 1
ORDER BY n DESC;

 shape        |  n
--------------+-----
 99999        | 790
 9999-99-99   |  40
 99/99/9999   |  12
              |   3

Digits become 9 and letters become a, so each distinct shape stands for a format. This column holds 790 serial numbers, 40 ISO dates, 12 slash dates and 3 blanks, and you know it before you write a conversion. The same trick works for amounts (is there a comma? a currency symbol?), ids (are they all the same length?) and phone numbers.

Other profiling checks worth keeping as a script:

• Leading and trailing whitespace: WHERE col <> trim(col).

• Non-breaking spaces and other invisibles: WHERE col ~ '[ ​]'.

• Case variants of the same value: group by lower(col) and compare the counts to the grouped-by-col counts.

• Out-of-range values: after casting a copy of the column, look at minimum and maximum.

• Row-level emptiness: rows where every data column is empty or null are separators.

Keep the profiling script in the repository and run it on every batch. When a finding repeats, turn it into a rule in the conversion step or a check that stops the load.

From the stage to the final tables

The conversion reads from the stage and writes the typed tables, using the rules the profile revealed. A good conversion has these properties: it names every column explicitly, it uses TRY_CAST-style conversions or pattern checks so a bad value becomes a reject and not an abort, it is wrapped in a transaction, and it records how many rows it inserted and rejected. The cluster guide shows a full pattern (getting Excel data into a database without heartbreak). The point of the stage is that you can re-run the conversion as often as you like against the same raw rows while you refine the rules, with no need to read the workbook again.

That reuse is worth stating plainly: reading a large XLSX takes real time, and a conversion rule has to be right the first time only if the raw data is gone. With a stage you iterate in SQL against data that is already loaded.

Retention and privacy

Staged rows are copies of whatever the sheet held, possibly personal data. Keep them as long as they serve a purpose and drop them afterwards. A sensible rule is to retain staged data for as long as the batch could still be disputed, for example 30 or 90 days after the load, then delete by batch_id:

DELETE FROM stage_orders
WHERE batch_id IN (SELECT batch_id FROM load_batches WHERE loaded_at < now() - interval '90 days');

Put the rule in the loader or a scheduled job, not in someone's memory. Restrict the staging schema to the loader and the people who investigate loads. If a sheet contains fields you never convert, such as free-text notes with personal details, consider not staging them at all: drop the columns when reading, and record in the batch that they were dropped.

Raw files deserve the same thought. Keep the workbook, or at least its hash, with the batch record. If the file is deleted and the stage is dropped, the batch table still shows what was loaded and when.

Variations by engine

MySQL. Use VARCHAR columns of a generous length or TEXT, with utf8mb4. Remember that MySQL's strict mode rejects an over-long string with an error in the stage, so size the columns for the longest plausible value. SQL mode and the collation affect comparisons in profiling queries.

SQLite. Declare every staging column TEXT, since integer affinity would convert 007 to 7 on insert (loading an Excel file into SQLite).

SQL Server. Use nvarchar columns. An OPENROWSET or wizard load can write straight into them (importing Excel data into SQL Server with OPENROWSET). TRY_CONVERT is the profiling companion.

Cloud warehouses. The stage is usually external files plus a COPY INTO into a transient table; the principle is the same.

When a staging table is more than the load needs

The data is tiny and trusted. A 20-row lookup list from a colleague you can ask does not need a stage. Insert it in one statement.

The source is already clean and typed. A CSV exported from a database, with ISO dates and no ambiguity, can go straight to a typed table with the engine's loader, which will fail clearly on a bad row.

You cannot keep copies. Some regulated data may not be duplicated into a second location. Then stage in a temporary table inside one transaction, and accept that you lose the ability to inspect after the fact.

The pipeline is streaming. For continuous feeds, a stage per batch becomes a queue. Use the platform's tools for that case.

PostgreSQL documents the bulk path in the COPY reference and unlogged tables in the CREATE TABLE page. For the other end of the method, validating row counts after an Excel import shows the checks to run on the stage. A small extract can be inspected as JSON with the CSV to JSON converter.