Your operations spreadsheet has a Customers tab, an Orders tab and formulas that decide which orders need attention. Importing both tabs into Postgres can leave you with the right row count and the wrong business behavior. The migration needs to preserve identifiers, relationships and the rules people use to do their work.
Start with a bounded workflow. Name the team that owns it, the files it reads and the decisions it produces. If people still need a shared editing interface after the move, include that application in the plan. A database alone does not replace the way a coordinator enters and corrects a record.
Inventory the workbook before exporting it
List every sheet and classify it as source data, reference data, calculation or output. Record the owner, update frequency and downstream consumer. Review formulas and manual instructions with the person who maintains the workbook. A colored cell or a note beside a table may carry an important exception rule.
Keep an unchanged workbook snapshot as the approved source for the rehearsal. Microsoft documents that CSV export saves the active sheet and does not preserve cell properties, formulas, graphics or formatting. Exporting one CSV is therefore not a workbook migration. Verify each required tab and preserve a separate description of the calculations.
Design records and relationships explicitly
Decide what one row means in each target table. A customer should have an identifier that remains stable when its display name changes. An order should reference that identifier, not a loosely typed customer name. Keep the original source key in a separate field when you introduce a new database identifier.
Postgres primary keys require unique, non-null values. Foreign keys check that related values exist in the referenced table. Use those controls to express the relationships the workbook previously implied. Resolve ambiguous matches with the operations owner before loading production. An AI suggestion can help organize a review queue, but it should not silently combine two customers.
| Spreadsheet pattern | Migration decision | Validation |
|---|---|---|
| Customer name repeated on orders | Create a stable customer reference | Every order resolves to the intended customer |
| Status typed freely | Approve a controlled vocabulary | Every old value has an explained mapping |
| Formula-derived total | Rebuild the calculation deliberately | Compare results on representative records |
| Blank cell | Define unknown versus empty | Check CSV quoting and null behavior |
| Multiple values in one cell | Consider a child table | Preserve the full set and parent link |
Load raw values into staging first
A staging table separates extraction from business conversion. Keep source values as text where that helps preserve the input, and track the originating file and row reference through your extraction process. Then apply explicit transformations into the final typed tables. This makes an invalid date or unexpected status a visible exception instead of an improvised correction.
Do not infer that an identifier is a quantity just because it contains digits. Agree how to preserve leading zeros and punctuation. Require unambiguous date exports. Keep currency and unit meanings beside numeric fields. If the workbook mixes meanings in one column, split the cases before choosing the target representation.
Choose the correct CSV import path
PostgreSQL COPY supports text, CSV and binary formats. With a file name, COPY reads from the database server's filesystem. A file sitting on an operator's laptop is not automatically available there. The psql client command called backslash-copy uses client-side files through the database connection. Choose the path that fits your deployment and access policy.
Use an explicit column list and verify the CSV field order. A normal HEADER option discards the header row. It does not map arbitrary file columns by their labels. PostgreSQL 17 documents HEADER MATCH for checking column names and order. Check the documentation for the version you actually run before adopting version-specific options.
Set the intended encoding and inspect exported text containing accented names or other non-ASCII characters. Do not assume the file uses the same encoding as the current database client. Keep a small fixture containing the characters and punctuation your real records use.
Test blanks, quotes and multiline cells
Postgres CSV handling distinguishes nulls and empty strings through quoting. With the default CSV settings, an unquoted empty field represents null and a quoted empty string represents an empty string. Decide whether your business needs that distinction, then test what the actual export produces. Avoid replacing every blank with zero or a generic placeholder.
CSV values can contain quoted commas, quotes and embedded newlines. A file can have more physical lines than records. Use a CSV-aware parser for validation rather than counting line breaks or splitting each line at commas. Include a multiline address and a note with quotation marks in the rehearsal dataset.
Validate the final tables and daily work
Reconcile source rows, staged rows, transformed records and approved exclusions separately. If normalization turns one row into several child records, document that expansion. Counts should explain the model rather than force unrelated tables to have identical totals.
Compare order amounts by customer and period, unresolved statuses and relationship gaps. Then ask operators to complete normal tasks in the replacement interface. Can they create an order, correct a customer and reproduce the exception report? Test the output of rebuilt calculations against approved examples, not just whether the query executes.
Postgres COPY FROM runs triggers and check constraints on the destination table. Review those behaviors before a load so the migration does not unexpectedly invoke application side effects. Load into an isolated rehearsal environment first. The same discipline applies to larger data migration projects.
Cut over with a controlled write boundary
- Get sign-off on the rehearsal results and unresolved exclusions.
- Choose the final workbook snapshot and stop edits to that source.
- Load the approved final data through the tested process.
- Reconcile the agreed counts, totals and relationships again.
- Switch users and downstream readers to the new workflow.
- Keep the source snapshot restricted for the approved retention period.
- Define rollback ownership before accepting new production writes.
If both systems accept edits, a rollback needs reconciliation of those changes. Simply opening the old workbook again can lose work completed after cutover. Prefer one clearly owned write path and a recorded decision point.
Common mistakes to avoid
- Exporting the visible sheet and missing lookup tabs.
- Treating a CSV header as automatic column mapping.
- Dropping import errors to reach a clean completion message.
- Recreating spreadsheet formulas without checking their intended meaning.
- Moving storage while leaving operators without a usable editing flow.
How Remqa helps with spreadsheet migrations
Remqa works on data, CRM, ATS and code migrations. An Excel-to-Postgres project can start with a workbook inventory, a target schema and one repeatable rehearsal. If the replacement also needs application changes, include the code migration scope. Bring the workbook and its operating instructions so the first discussion covers the workflow as well as the rows.