Moving From Excel to PostgreSQL Step by Step Without Loss

A workbook that several teams depend on is a good candidate for PostgreSQL: concurrent writers, access control, constraints, history and a path to growth. The move is easy to describe and hard to do carelessly, because a spreadsheet hides its problems and a database shows them. This post gives the sequence for a careful move: stage, clean, design, load, verify, secure and reconnect. It assumes a PostgreSQL server you can create tables on. It is a spoke of when a spreadsheet has become a database.

Before you start

Freeze the workbook: copy it, note the date, and tell people that edits made after this point will not be in the migration. Decide what the target must hold, who writes, who reads, and what history matters. Create a database and three roles in advance, a loader that owns the tables, a writer for the application or the people who edit, and a reader for reports:

CREATE DATABASE shop;
CREATE ROLE shop_loader LOGIN PASSWORD 'set-from-a-secret-store';
CREATE ROLE shop_writer LOGIN PASSWORD 'set-from-a-secret-store';
CREATE ROLE shop_reader LOGIN PASSWORD 'set-from-a-secret-store';

Use a secret store or a password manager for the real values. The placeholder text above is not a password to use.

Step 1: export the sheet cleanly

Take the data out of Excel in a form that does not change it. Export each data sheet to CSV (UTF-8) from a script, not by hand, and keep everything as text:

import pandas as pd

sheets = pd.read_excel("orders.xlsx", sheet_name=None, dtype=str)
for name, df in sheets.items():
    df.to_csv(f"{name}.csv", index=False, encoding="utf-8")

dtype=str stops pandas from turning identifiers into numbers and dates into timestamps. Keeping values as text at this stage makes the load lossless; typing happens later, where you can see the failures (importing an Excel sheet into PostgreSQL with Python shows the direct route).

Step 2: load into a staging table

Create a staging table with all columns as text and load the file with \copy:

CREATE SCHEMA staging;
CREATE TABLE staging.orders_raw (
    order_no text, order_date text, customer text, address text,
    product text, qty text, price text
);

psql -d shop -c "\copy staging.orders_raw FROM 'Orders.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')"

\copy runs on the client, so the file does not need to be on the server. Count the rows and compare with the sheet: SELECT COUNT(*) FROM staging.orders_raw;. Nothing has been cleaned or changed yet; this is the raw evidence, and it stays in the database as an audit copy.

Step 3: profile and clean in SQL

With the data in tables, SQL does the cleaning well. Profile first:

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT order_no) AS orders,
       COUNT(*) FILTER (WHERE btrim(customer) = '') AS blank_customer,
       COUNT(*) FILTER (WHERE price !~ '^\d+(\.\d+)?$') AS bad_price,
       COUNT(*) FILTER (WHERE order_date !~ '^\d{4}-\d{2}-\d{2}$') AS bad_date
FROM staging.orders_raw;

Each count is a question for the data's owner. Fix the cheap ones with UPDATE on a copy (trim, normalize case, map spelling variants through a lookup table), and list the expensive ones for a human (standardizing categories with a lookup table). Make a rule never to edit the raw staging table: create staging.orders_clean as a view or a table derived from it, so the original remains.

Step 4: design the tables

Decide what entities the sheet holds and give each a table with a key. Use proper types: integer or bigint for ids, numeric(12,2) for money, date for dates, text for text, and boolean where the sheet has yes/no (designing tables from a messy Excel sheet). Add the constraints the sheet could not express:

CREATE TABLE customers (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    text NOT NULL UNIQUE,
    address text
);
CREATE TABLE products (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL UNIQUE,
    price numeric(12,2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    order_date  date NOT NULL,
    customer_id bigint NOT NULL REFERENCES customers(id)
);
CREATE TABLE order_lines (
    order_id   bigint NOT NULL REFERENCES orders(id),
    product_id bigint NOT NULL REFERENCES products(id),
    quantity   integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

Keep identity columns for rows the sheet had no id for, and use the sheet's own id where a stable one exists, such as the order number.

Step 5: load the real tables

Insert from the clean data in dependency order, in a single transaction, casting as you go:

BEGIN;

INSERT INTO customers (name, address)
SELECT DISTINCT ON (btrim(customer)) btrim(customer), NULLIF(btrim(address), '')
FROM staging.orders_raw
ORDER BY btrim(customer);

INSERT INTO products (name, price)
SELECT DISTINCT ON (btrim(product)) btrim(product), price::numeric
FROM staging.orders_raw
ORDER BY btrim(product);

INSERT INTO orders (id, order_date, customer_id)
SELECT DISTINCT r.order_no::bigint, r.order_date::date, c.id
FROM staging.orders_raw r JOIN customers c ON c.name = btrim(r.customer);

INSERT INTO order_lines
SELECT r.order_no::bigint, p.id, r.qty::integer, r.price::numeric
FROM staging.orders_raw r JOIN products p ON p.name = btrim(r.product);

COMMIT;

A cast that fails aborts the whole transaction with a message naming the offending value, which is what you want. Casting is also how you find rows the profile missed. If a constraint is violated, find the row, decide what is true and fix the data. Resist the urge to drop the constraint.

Using an inner join silently drops rows that do not match, for instance a line whose customer has a trailing space the cleaning missed. The verification step catches that.

Step 6: verify

Compare the database with the workbook on counts and totals:

SELECT (SELECT COUNT(*) FROM staging.orders_raw)     AS staged_rows,
       (SELECT COUNT(*) FROM order_lines)            AS loaded_lines,
       (SELECT SUM(price::numeric * qty::integer) FROM staging.orders_raw) AS staged_total,
       (SELECT SUM(quantity * unit_price) FROM order_lines)                 AS loaded_total;

Staged rows should equal loaded lines, and the totals should match, unless you deliberately excluded rows, which you should list. Check the Excel side as well: the same total computed in the original workbook should equal the database total. Look at five random rows end to end. Save the results in a note with the date (validating row counts after an Excel import).

Step 7: roles and access

Give each role what it needs and nothing more:

GRANT USAGE ON SCHEMA public TO shop_writer, shop_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_reader;
GRANT SELECT, INSERT, UPDATE ON orders, order_lines TO shop_writer;
GRANT SELECT ON customers, products TO shop_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO shop_reader;

Readers cannot change anything. Writers can add and change orders but not customers or products. Nobody but the loader can drop a table. This is the access control the workbook never had (giving Excel users a read-only database account). Restrict network access with pg_hba.conf or the host's firewall, and require TLS for connections that leave the machine.

Step 8: backups

Take a dump right after the load, and schedule regular ones: pg_dump -Fc shop > shop_$(date +%F).dump. Store them off the server and test a restore on a scratch database. Keep the original workbook and the CSV exports with the migration note.

Step 9: reconnect Excel

People still want their spreadsheets. Give them a connection that reads: Data, Get Data, From Database, From PostgreSQL Database, with the reader role and a query, or an ODBC connection (connecting Excel to PostgreSQL with ODBC). Build the pivots on the query, so they refresh. Writes should go through a controlled path such as a form or a script, not a free-for-all from Excel (keeping Excel as the front end to a database).

Cutover

Choose a date, freeze the workbook at the last moment, load the final changes, verify and announce. Keep the workbook read-only as an archive. Plan for the first week: people will ask for things the sheet allowed, such as editing any cell. Have a quick way to handle requests, and fix the process rather than reopening the workbook.

When a PostgreSQL server is more than the job needs

One or two writers, a small team. SQLite gives you a database with no server to run (moving from Excel to SQLite for a small team).

No one to run it. A server needs patches, backups and monitoring. Use a managed service or a simpler option.

The data is a few hundred rows. The workbook may be fine, with better habits.

You need a form-based tool for staff. A database alone is not a user interface. Plan for the front end, or the move will fail on usability.

See the PostgreSQL manual pages on COPY, constraints and database roles. To convert a small extract, use the CSV to JSON converter.