Skip to content
Download
USE CASE

Retire CSV import and export, edit the table live

Export to CSV, fix it in Excel, import it back, and pray the load job does not choke on a comma. Workbook Connect points Excel straight at the table: no file to version or email, a per-row change log built in, foreign-key lookups that keep references valid, and your validation rules running before commit.

Download free

No file. No round trip. Just the live table

Open dbo.DimSupplier in Excel, edit the rows you need, and commit. What you see is the current data, and what you commit goes straight back. The CSV in the middle is gone.

EXCELDATABASE

The CSV round trip costs you more than a comma

Anyone who has run the export-edit-import loop knows the failure modes by heart. The file itself is the first problem: it is a copy, stale the moment a colleague edits the source, and it ends up versioned in a folder or forwarded round in email, so you can never quite prove which one is current. Excel then reformats the export on open, coercing SupplierCode 0142 into the number 142, a date into a serial number, and a long reference into 1.23E+11. Two people edit their own copies and the second import simply overwrites the first, last file wins, with no record of what changed. Someone inserts a column and every value after it shifts, so a country code lands in the wrong field and the load job either rejects the whole file on a single bad row or, worse, accepts the garbage and you find out in a report a fortnight later. None of this is your edit going wrong. It is the file in the middle.

The table you actually want to edit

Most CSV loads target a dimension like DimSupplier, with a foreign key to a reference table such as DimCountry. Workbook Connect edits DimSupplier in place and uses that foreign key to keep every country valid, so the round trip through a file is never needed.

∞1CountryIDDimSupplierSupplierIDPKSupplierCodeNameCountryIDFKPaymentTermsDaysIsActiveDimCountryCountryIDPKISOCodeName

An illustrative data model, not your exact schema.

Open the table in Excel, not a snapshot of it

Workbook Connect connects Excel directly to DimSupplier and pulls the current rows over a live connection. There is nothing to export first, and what you see is the data as it stands right now, not a file that went stale the moment a colleague edited the source. When you fill in CountryID, a foreign-key lookup offers the real entries from DimCountry, so you pick Sweden rather than fat-fingering an ISO code that does not exist. The shared configuration that drives all of this, the lookups, the filters, the primary-key mapping, the hidden technical columns, is set once and pushed to the team automatically on connect, so everyone opens the same sheet instead of their own ad hoc copy of a CSV. And if you have authored validation rules in the Validation Designer (a Pro feature), they run in the sheet to hold bad rows back before commit.

A supplier edit, with the mistake CSV would have hidden

Update terms, add suppliers, and watch the per-row status track each change. The integer column PaymentTermsDays rejects 14,90 before commit, where a CSV import would have quietly truncated or rejected the whole load.

Suppliers.xlsx (DimSupplier)
StatusSupplierCodeNameCountryPaymentTermsDaysIsActive
ChangedSUP-0142Nordic Steel ABSE Sweden30true
SUP-0143Rhine Logistics GmbHDE Germany60true
NewSUP-0207Iberia Packaging SLES Spain30true
Needs fixSUP-0208Helios Components OyFI Finland14,90true
ChangedSUP-0095Atlas Foundry LtdGB United Kingdom45false
NewSUP-0096Lumen Optics BVNL Netherlands30true
Commit Validated, then written to dbo.DimSupplier
  • Row 1: payment terms moved from 45 to 30. The status column marks it Changed; only the changed cells of the row are written on commit.
  • Row 4: 14,90 fails the type check on PaymentTermsDays, the row you need to fix, so the commit is blocked until it is corrected. Nothing is written until every row passes, then they all commit together. A CSV import would have coerced or truncated 14,90 silently.

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

Validated first, then committed in one transaction

Workbook Connect validates the whole sheet in Excel first. Before anything is written, every value is checked against its column type, so a non-number in PaymentTermsDays or too many decimals for the column is flagged in the sheet on its own, independent of any rule you author. On top of that, your validation rules run in the sheet: a rule on PaymentTermsDays holds 14,90 back with the reason attached, a country that does not resolve to DimCountry is rejected by the foreign-key lookup, and a rule requiring SupplierCode keeps a blank one from making it through. The per-row status column marks each line New, Changed, or flagged for deletion, so you can see at a glance exactly what the commit will do. 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 dbo.DimSupplier together in one transaction, under the permissions you already hold, with no file parser in the path. If a colleague changed the same supplier after you opened the grid, optimistic concurrency catches it instead of letting a stale file win.

Works with your database

FAQ

Replacing CSV, answered

No. Workbook Connect reads the table over a live, encrypted connection (TLS 1.2 or higher) and writes your edits straight back on commit. Nothing is exported to disk, attached to an email, or fed through a parser, so the encoding, delimiter, and quoting problems that come with CSV simply do not exist.

CSV is where 14,90 turns into 1490 and a leading-zero code becomes a number, because the file is reparsed and the values are coerced on the way in and out. Workbook Connect never round-trips through a file, so that coercion step never happens. To catch the rest, every value is checked against its column type before commit, so 14,90 in the integer PaymentTermsDays column is flagged with its reason instead of slipping through, and foreign-key lookups reject a value that does not resolve to a real reference. You can also author your own rules in the Validation Designer (a Pro feature), enforced in the sheet for everyone. Either way you see the problem in Excel before anything reaches the database, not three weeks later in a report.

A shifted column usually means a value lands under the wrong header, for example a country code sitting under CountryID. When a value lands in a foreign-key column, the foreign-key lookup catches it on commit: a code that does not resolve to a real DimCountry entry is rejected rather than written. For other columns, a validation rule you have authored holds the bad value back with its reason. With a CSV import the misaligned row often loads anyway and corrupts the table quietly.

In one grid. Edit a cell and the row is marked Changed, type into a blank row and it is marked New, flag a row and it is marked for deletion. On Commit, all three happen in a single transaction against the table you chose, for example dbo.DimSupplier, so you never end up with half a load applied.

No. Workbook Connect uses optimistic concurrency. If someone changed PaymentTermsDays for SUP-0142 after you loaded the grid, the conflict is detected on commit and surfaced to you, rather than the last file overwriting whatever was there.

No. Workbook Connect connects to your existing tables and makes no server-side schema changes. People sign in with the authentication you already use, including Entra ID with MFA, and every read and write honours the database permissions they already have.

Related use cases

Delete the CSV step for good

Download Workbook Connect free, connect it to your database, and edit the table the way you wish you always could: live in Excel, validated before commit, with no file to export, mangle, or re-import.

Download free