Skip to content
Download
USE CASE

Cleanse and correct your data, straight from Excel

Blank emails, phone numbers in five different formats, a CountryID that points at nothing. Workbook Connect lets you find those rows, fix them in the grid, and write the corrections back to your database, with your validation rules checking every value first.

Download free

Fix it in the sheet, correct it in the database

Browse to the bad rows, overwrite the values, fill the gaps, and commit. The corrections land in the live table the moment validation passes.

EXCELDATABASE

The real cost of a dirty dimension table

A customer dimension rots quietly. Someone imports a list with phone numbers as "09 4250113", a web form lets an email through as "post[at]oslomarine.no", a migration leaves a few hundred CountryID values NULL. None of it breaks anything on the day, so it sits there. Then a mailing bounces, a country-level report is short a region, and a join to DimCountry silently drops the orphaned rows. The usual fix is to export the bad rows to a spreadsheet, mark them up by hand, and hand them to whoever can write the UPDATE, then wait, then reconcile what actually changed. Half the time the corrected file and the table have already drifted apart by the time the change lands.

Where the dirt actually lives

Most cleansing work happens in a dimension table full of nullable, free-text columns. DimCustomer holds the values people get wrong, and its CountryID foreign key into DimCountry is exactly the kind of link that goes stale.

∞1CountryIDDimCustomerCustomerIDPKNameEmailPhoneCountryIDFKIsActiveDimCountryCountryIDPKISO2Name

An illustrative data model, not your exact schema.

Find the bad rows without writing a query

Open Excel, connect to the table, and filter down to the problem set: the rows where Email is blank, where Phone does not start with a plus, where CountryID is NULL. You are looking at the live table, so what you filter is the real data, not a week-old extract. The setup that makes this safe (the DimCountry lookup on CountryID, the filters, the primary-key mapping, and the technical columns hidden from view) is configured once by a Configurator and shared to the whole team automatically on connect, so everyone opens the same guard-railed sheet.

DimCustomer, mid-cleanse

Fill a blank, resolve a broken foreign key, reformat a phone number, and watch the per-row status track each one. The row whose email still fails the format rule blocks the commit; nothing is written until every row passes, then they all commit together.

Customers.xlsx, DimCustomer
StatusCustomerIDNameEmailPhoneCountry
10241Nordtek ABinvoice@nordtek.se+46 8 555 0142SE Sweden
Changed10242Bauhaus Möbel GmbHinfo@bauhaus-moebel.de+49 30 901 8820DE Germany
Changed10243Vlaamse Drukkerijsales@vldruk.be+32 3 226 7711BE Belgium
Changed10244Helsinki Logistics Oykirjaamo@hel-log.fi+358 9 4250 113FI Finland
Needs fix10245Oslo Marine ASpost[at]oslomarine.no+47 22 80 11 90NO Norway
New10246Lisboa Têxtil Ldageral@lisboatextil.pt+351 21 360 4400PT Portugal
Commit Validated, then written to dbo.DimCustomer
  • Row 2: Email was blank in the source. Filling info@bauhaus-moebel.de marks the row Changed; it commits because it now passes the email-format rule.
  • Row 3: CountryID was NULL. Picking BE Belgium from the DimCountry lookup resolves the foreign key, so the row is no longer orphaned.
  • Row 4: Phone was stored as "09 4250113" with no country code. Overwriting it with the E.164 form +358 9 4250 113 corrects the value in place.
  • Row 5: Email "post[at]oslomarine.no" still fails the format rule (no @ sign), so the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together.

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

Every correction is checked before it lands

As you edit, a per-row status column marks each line New, Changed, or flagged for deletion, so you can see exactly what you are about to write. On commit, your validation rules run first: an Email has to match a real address pattern, a Phone has to be in E.164 form, CountryID has to resolve to a row in DimCountry. Workbook Connect validates the whole sheet in Excel first, and if any row fails the commit is blocked and nothing is written; the failing rows stay in the sheet with their reasons, so you fix them and commit again, and then every row is written to DimCustomer together in a single transaction under the permissions you already hold. A half-fixed email never reaches the table. Optimistic concurrency catches the case where someone else edited the same customer since you loaded it, so your correction and theirs never silently clobber each other. Authoring those rules in the Validation Designer is a Pro feature, but the rules themselves are enforced for everyone on every connection.

Works with your database

FAQ

Data cleansing, answered

Yes. You edit a cell in place exactly as you would in any spreadsheet, and the per-row status flips to Changed. You can also add rows (status New) and flag rows for deletion. On commit, every cell you touched is written back, and untouched cells are left alone.

Filter the table to the rows where the field is empty (for example, DimCustomer rows with no Email or a NULL CountryID), enter the values in the grid, and commit them all in one transaction. There is no UPDATE statement to write and no CSV to re-import.

Validation runs before anything is written. Workbook Connect validates the whole sheet in Excel first, and if any row fails, say an email with no @ sign, or a Phone that is not in E.164 format, the commit is blocked and nothing is written. The failing rows stay in the sheet with their reasons, so you fix them and commit again, and then every row is written together in one transaction. Foreign-key lookups also mean you can only point CountryID at a value that exists in DimCountry.

You are editing the live table, so you see the real state, but nothing is written until you commit. The write goes through your existing database permissions over TLS 1.2 or higher, optimistic concurrency catches anyone who changed the same row since you loaded it, and Workbook Connect makes no server-side schema changes.

A Configurator sets up each table once (the lookups, filters, primary-key mapping, and which columns are visible) and that configuration is shared to the team automatically on connect. Validation rules are enforced for everyone on every connection, including the free plan, so a User opens a sheet that already has the guard rails in place.

Related use cases

Clean up your data where you can see it

Download Workbook Connect free, connect it to your database, and start correcting the rows you can actually see, with every value validated before it touches the table.

Download free