Data cleaning, without shipping the data anywhere

The landscape, described fairly

If you want free, local and genuinely powerful and do not mind learning its model, OpenRefine is excellent and has been for years. It is the honest first recommendation for one-off cleanup of a messy dataset, and we would rather say so than pretend the category starts with us.

The part most tools get wrong: the fix has to survive

Cleaning a file once is easy. The failure mode is next month, when the same export arrives with the same problems, and the fix you made was a find-and-replace you cannot remember the parameters of.

In TableDI a cleanup step is a formula column, which is a property of the column rather than an edit to a range. It recalculates for every row, including rows pasted in later. Six months on, the column is still there, the formula is still readable, and nobody has to remember anything. That is the whole argument, and it is why the guided panels matter more than the function count.

The eight operations, and the panel that writes each one

• Trim and case — leading spaces from a copy-paste, ACME versus Acme. The usual reason a match fails.
• Split a column — one Name into first and last, an address into parts, a timestamp into date and time. The Split and Split date-time panels.
• Merge columns — the reverse, with a separator you choose. The Merge panel.
• Deduplicate — the Dedupe panel removes repeats; Combine duplicates keeps one row and sums or concatenates the rest, which is usually what people actually wanted.
• Match against another table — the Match and Vlookup panels, four steps each, no argument order to remember. How the panels work.
• Summarize — group and aggregate without building a pivot first.
• Union — stack two tables with different column orders into one.
• Flag what is still wrong — a formula column that marks rows failing your rule, so bad data is visible rather than silently averaged in.

Where AI helps with cleaning, and where it does not

AI is good at the judgment calls that rules handle badly: deciding that "Acme Corp.", "ACME Corporation" and "acme corp" are one company; classifying a free-text column into categories; pulling a date out of a description field written by a human. In TableDI that is the ai.generate function, used in a formula so it runs down the column and its output is a real column other formulas can build on.

AI is the wrong tool for anything deterministic. Trimming spaces, comparing numbers, checking a date range — a rule is faster, free, and cannot be creative on a Tuesday. Two things worth stating plainly: a model given a hundred thousand rows will get some of them wrong, so spot-check before you build on the result; and AI requests use your own API key and go from your machine to your provider, so the rows you send are covered by the account you hold with them. More on that trust model.

Cleaning data that should not leave the building

The datasets that most need cleaning are customer lists, HR exports, patient records and finance extracts — the ones people are least free to paste into a web tool. That constraint quietly rules out a large part of this category, and it is why "runs on your own machine" is a functional requirement here rather than a preference. TableDI is a desktop application: the workspace is a folder on your disk, there is no account, and the app works with no network at all unless you ask it to fetch something.