Skip to content
Download
USE CASE

Data validation in Excel, before anything is committed

Author the rules your tables have to meet, then let Workbook Connect check every edit in the spreadsheet. If any row fails, the commit is blocked and nothing is written until every row passes, then they all commit together in one transaction.

Download free

Nothing reaches the database until it passes

Edit in Excel, run the rules, fix what fails, then commit. Invalid rows stay in the sheet with a reason. They never make it to your database.

EXCELDATABASE

The export-edit-import loop validates nothing

Here is the loop most teams actually run. Someone exports SalesOrder to a CSV, the data gets edited in Excel for a week, and then it is bulk-loaded back. Nothing in that round trip checks the data. A pasted CustomerID of C-777 that does not exist, a StatusCode typed as SHPD instead of SHIP, a ShipByDate left blank on an open order: all of it loads, or worse, half of it loads and the batch fails on row 4,000 with a foreign-key violation and no clue which CustomerID caused it. By then the cleanup is a forensic exercise. The data either sits broken in the database or you spend the afternoon diffing two files to find the eleven rows that went wrong.

What a row actually has to satisfy

A SalesOrder is only valid in context. Its CustomerID has to resolve to a real customer, its StatusCode has to be a known status, and its dates and amounts have to be present and in range. Your validation rules check these column by column, and the keys in your schema back them up.

∞1CustomerID∞1StatusCodeDimCustomerCustomerIDPKNameCreditHoldSalesOrderOrderIDPKCustomerIDFKStatusCodeFKOrderDateShipByDateTotalAmountDimOrderStatusStatusCodePKDescriptionIsOpen

An illustrative data model, not your exact schema.

Author the rules once, in plain terms

In the Validation Designer a Configurator writes the rules each table has to meet, in terms of the columns themselves rather than SQL. For SalesOrder that might be: TotalAmount is required and cannot be negative, ShipByDate is required, and must be on or after the earliest allowed ship date, and CustomerID and StatusCode must resolve through their foreign-key lookups. You write these once. From that point they are enforced for everyone who opens that table, on every connection, in the free plan and the User role included. The standard no longer lives in one analyst's head or in a tab of manual checks that gets skipped under deadline.

Validation catching real mistakes, row by row

Six edits queued against SalesOrder. Two are clean and ready. Four fail for four different reasons, and while any of them fails the whole commit is blocked, each one showing exactly what went wrong.

Orders-June.xlsx · SalesOrder
StatusOrderCustomerStatusShip byTotal
ChangedSO-10412C-204 NorthwindOPEN2026-06-22€ 8,640.00
NewSO-10413C-118 Acme FoodsOPEN2026-06-24€ 2,310.50
Needs fixSO-10414C-777 GlobexOPEN2026-06-25€ 4,120.00
Needs fixSO-10415C-204 NorthwindSHPD2026-06-20€ 950.00
Needs fixSO-10416C-118 Acme FoodsOPEN2026-06-12€ 1,775.00
Needs fixSO-10417C-330 InitechOPEN€ 6,200.00
Commit Validated, then written to dbo.SalesOrder
  • SO-10414: CustomerID C-777 has no match in DimCustomer. Unknown foreign key, so the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together.
  • SO-10415: StatusCode SHPD is not in DimOrderStatus (the valid code is SHIP). Unknown status code, so the commit is blocked until this row is fixed.
  • SO-10416: ShipByDate 2026-06-12 is before the earliest allowed ship date 2026-06-15. Below the minimum value, so the commit is blocked until this row is fixed.
  • SO-10417: ShipByDate is blank. The required-field rule flags it, so the commit is blocked until this row is fixed.

An illustrative example of the editing workflow, not a product screenshot.

One commit, all or nothing

As you edit, the per-row status column tracks every line as new, changed, or flagged for deletion, so you can see the shape of the change before you act on it. When you commit, the rules run first against the whole sheet. In the example above, four rows fail, so the commit is blocked and nothing is written, not even the two clean rows. The failing rows stay behind in the sheet with their reasons. You correct C-777 to a real customer, fix SHPD to SHIP, give the blank or too-early ShipByDate a valid value, and commit again. Now that every row passes, all six are written to dbo.SalesOrder together in a single transaction. Everything happens under the database permissions you already have, and if a teammate changed one of these orders since you loaded it, optimistic concurrency catches the conflict on commit instead of letting one of you quietly overwrite the other.

Works with your database

FAQ

Data validation, answered

Each rule checks one column against a fixed standard: required fields (ShipByDate cannot be blank), value ranges (TotalAmount cannot be negative, ShipByDate must be on or after a fixed date), allowed or disallowed sets, formats and patterns, length, and per-column uniqueness. These sit on top of the foreign-key and primary-key constraints already in your schema, so a CustomerID or StatusCode that does not resolve is caught too. For a comparison between two columns in the same row, use a database CHECK constraint, which Workbook Connect surfaces as the backstop.

Authoring with the Validation Designer is a Pro feature, done by a Configurator. Once the rules exist they are enforced for every person on every connection, including the free plan and including the User role. Standards stop depending on who happens to be editing that day.

Workbook Connect validates the whole sheet in Excel first. If any row fails, the commit is blocked and nothing is written. The failing rows stay in the sheet with their reasons, for example "ShipByDate is before the earliest allowed ship date", so you fix them and commit again, and then every row is written together in one transaction. Nothing invalid reaches the database in the meantime.

No, it works in front of them. Workbook Connect makes no server-side or schema changes. Your CHECK constraints, foreign keys, and triggers stay exactly as they are. Validation rules catch problems in Excel first, so a contributor sees a plain reason in the grid instead of a raw SQL constraint error after the fact.

When a table is configured with a foreign-key lookup, picking a CustomerID or StatusCode offers the real values from DimCustomer and DimOrderStatus as you type, so most unknown-reference errors never get entered. Validation is the backstop for the ones that slip in from a paste or an import.

Yes. A per-row status column marks each line as new, changed, or flagged for deletion as you work. When every row passes, the commit writes them together in one transaction under your existing database permissions, and conflicting edits from another user are detected at that point rather than silently overwritten.

Related use cases

Stop bad data at the spreadsheet

Download Workbook Connect free and connect it to your database. Let your rules decide what reaches SalesOrder, and let the four kinds of mistake above die in the sheet instead of in a failed load.

Download free