A scraped product feed lands with 2,300 rows, and the vendor column holds five spellings of one company: Apple, Apple Inc., Apple, Inc., APPLE INC, apple inc. Group by vendor and you get five vendors. Pulling the pages was the part you scheduled. Turning what came back into something you can group, sum and join is the part that eats the afternoon.
OpenRefine is the tool most people reach for, and it is the same program you may remember as Google Refine. This walkthrough covers importing a scraped file, faceting it, clustering inconsistent values, transforming columns with expressions, and matching free text against a knowledge base. Every version number, default, quotation and figure below was read from the project's own repository, documentation, release pages, blog and forum on 10 August 2026, against OpenRefine 3.10.1. Three claims that travel through older write-ups, the earlier version of this one included, do not survive that recheck: that OpenRefine sends nothing anywhere, that it still reads Google Sheets out of the box, and that a saved recipe cleans next month's file by itself.
What a dirty column actually costs
Deduplication feels like clerical work, so it gets scoped as clerical work. The arithmetic says otherwise. Take 2,300 scraped vendor strings covering roughly 400 real companies. Merging them by eye means reading every value, deciding, and typing the canonical form. At three seconds a decision, with no mistakes and no interruptions, that is close to two hours. In a spreadsheet those two hours buy one cleaned file, and nothing about the process survives, so next month's scrape starts them over.
Then there is the part that costs more than time. A 2% mis-grouping rate across 10,000 rows leaves 200 rows attributed to the wrong entity. If those rows carry prices, every average you publish is wrong by an amount nobody can reconstruct later, and no stage of the pipeline raises an error. Bad joins are silent. That is what separates them from crashes.
OpenRefine's answer is that every action is stored as an operation, in order, as JSON you can copy out and replay. Cleaning stops being a file and becomes a procedure.
The three names, and the one thing that actually broke
The project states its own history in the README. The software was created at Metaweb Technologies and conceived by David Huynh. Metaweb was acquired by Google in July 2010 and the product was renamed Google Refine. In October 2012 it became OpenRefine as it moved to a community-driven model, and since 2020 it has been fiscally sponsored by Code for Science and Society. So a search result pointing at Google Refine is not far out of date: the menus are recognizable and almost every old tutorial still applies.
The exception is reconciliation, and it is a break rather than a rename. Those tutorials matched values against Freebase, the knowledge base that came with Metaweb. Google's own Freebase page states that the API has been shut down, that the service ran from 2007 to 2015, and that "The Freebase Search API has been retired as of August 31, 2016." Read the Freebase half of any old guide as an argument about method, not as instructions.
Is it maintained? Read the funding, not the version number
"Actively maintained" is the phrase every roundup uses and nobody checks, and for a free desktop tool the release cadence is only half the answer.
The code is moving. OpenRefine 3.10.0 shipped on 26 February 2026, adding XZ, LZMA, 7zip, ZStandard and Unix compress archives on import. OpenRefine 3.10.1 followed on 4 March 2026, and its notes say it plainly: "This is the second stable release of the 3.10 series. It is identical to 3.10.0 in every way except that the Mac build is notarized." The repository shows 11.9k stars, 684 open issues and 48 open pull requests, the 2025 year-in-review names 51 contributors, and the community forum carried new posts on the morning this article was checked. That review also reports that "Version 3.9.5, released in September, has already seen over 70,000 downloads, averaging close to 20,000 downloads per month."
The money is the part nobody quotes. In December 2025 the project reached the end of its EOSS 5 grant, closing five years of Chan Zuckerberg Initiative support. The fundraising campaign that followed raised approximately USD 595: five one-time donations worth USD 317.21, and a merchandise store that turned 13 orders and USD 899.86 of sales into USD 107 of profit. Eight recurring donors give USD 67 per month. The project's own conclusion is not softened: community giving alone is not sufficient to sustain the core team.
And the staffing has a date on it. The minutes of the 6 August 2026 advisory committee meeting with Code for Science and Society, posted on the project forum, record that "The managing director role is funded until September 30, 2026," that OpenRefine has entered dormant status with its fiscal sponsor, and that it "needs an annual income of at least USD 25,000 to exit the dormant status." That is $25,000 a year against roughly $800 of recurring community giving. A proposal to the Open Technology Fund was not accepted, and the Mac snapshot builds have been failing for months. A year earlier the long-time maintainer had published a handover post: he had "left the advisory committee last year, the core dev group this year and stopped developing and maintaining OpenRefine a few months ago."
None of that is a reason to avoid the tool, and the reason matters. OpenRefine is free and BSD-licensed, runs on your own machine, and has no account and no subscription to lapse. A project going quiet takes nothing away from you: the 3.10.1 you installed keeps opening files in 2030. A hosted cleaner going quiet takes your data and your saved rules with it. Budget for the version you have, not the version you hope for.
Installing it, and the Java version that nobody states correctly
OpenRefine has an unusual shape for a desktop application: it runs a small web server on your own machine and you drive it through a browser tab. On launch it opens http://127.0.0.1:3333/, a local address rather than a cloud one.
The Java question has three answers in circulation, and two of them are stale.
- The system requirements page still reads "OpenRefine works with Java 11 to Java 17 for OpenRefine 3.7," three minor releases behind the software it documents.
- The build configuration of the 3.10 tags is the one to trust: minimum Java 11, maximum Java 21.
- The development branch has already moved to Java 21 through 26, so the next feature release drops 11 through 17. Pinning a runtime for a shared image? Pin 21.
The macOS package includes Java, and Windows packages have bundled a runtime since 3.4. The Linux tarball does not.
Memory decides whether it feels fast. The shipped refine.ini sets REFINE_MEMORY=1400M, while the manual's memory section says the default is 1 GB. Two numbers, both in the project's own files. Check yours rather than trusting either, and raise it before a big import, not after the first crash:
./refine -m 6000M Linux and macOS
refine /m 6000M WindowsThe manual's threshold for "large" is concrete: more than one million total cells, an input file over 50 MB, or more than 50 rows per record. At that size it advises switching off "Parse cell text into numbers, dates, ..." on import and converting only the columns you need.
One security note. OpenRefine listens only on 127.0.0.1, and the manual's caution about binding it elsewhere is blunt: if the machine has an external IP, do not, without a proxy or firewall in front. There is no authentication layer behind it.
Loading a scraped file
OpenRefine reads comma and tab separated text, fixed-width columns, JSON, XML, Excel, ODS, MARC, Wikitext and RDF. It opens archives directly, detecting the files inside .zip, .tar.gz, .tgz, .tar.bz2, .gz and .bz2, with .rar the documented exception, and 3.10 added XZ, LZMA, 7zip, ZStandard and .Z. You can load several files into one project and keep the source filename as a column.
Google Sheets is the entry that quietly changed. Older guides, this one included, list Google Sheets among the formats OpenRefine reads. It shipped as a bundled gdata extension through 3.8.7. From 3.9.0 the bundle contains four extensions, jython, wikibase, database and pc-axis, and gdata is not among them. It now sits on the extensions page as a separate download listed for 3.9. If your workflow starts in a Sheet, you install something first.
Choose Create Project, point it at the file, and confirm the delimiter, header row and encoding in the preview. Set the encoding to UTF-8 explicitly. Nothing you do afterwards touches the original file: OpenRefine writes to its own workspace.
Faceting: finding the mess
A facet summarizes a column so you can see and isolate its distinct values. Open a column's dropdown and choose Facet.
Text facet is the one you will live in. It lists every unique value with a count, and a scraped column confesses immediately: USA, U.S.A., United States and US as four entries with four counts. Click a value and the grid narrows to those rows, ready to edit as a group. Sort by count and read the tail: values appearing once are usually the pages where your selector missed.
Numeric facet gives a histogram with a range slider, which catches the price that arrived as 0 or 999999, and timeline facet does the same for dates. Custom facet is built from an expression, and two belong in a snippet file: value.length() exposes truncated records, isBlank(value) counts the pages where a field never came through.
Clustering: what the fingerprint really does
Merging Apple, Apple Inc. and APPLE INC by hand does not scale past a few hundred rows. Clustering does it by algorithm: open a text facet and click Cluster.
The default method is key collision by fingerprint, and the surprises follow from its steps: trim outer whitespace, lowercase, remove punctuation and control characters, normalize extended western characters to ASCII, split on whitespace, sort the tokens and drop duplicates, then join them back with single spaces.
"Apple Inc." -> "apple inc"
"APPLE, INC." -> "apple inc"
"Inc. Apple" -> "apple inc"
"Applé Inc." -> "apple inc"Two consequences fall out of that. Token order does not matter, so "Cruise, Tom" and "Tom Cruise" land in the same bucket. And the diacritic folding is deliberately lossy: the manual offers "gödel" and "godél" as a documented false positive, both fingerprinting to godel while being plausibly different names. The source file FingerprintKeyer.java goes further than the prose does, mapping ß to ss, æ to ae, ø to oe and the Icelandic þ to th. For scraped European company names that is usually what you want. Where those characters carry meaning, it is a hazard.
The n-gram method is routinely described wrongly. Roundups say it catches word-order differences. Plain fingerprinting already does that, because it sorts the tokens. What n-gram adds is tolerance for letters in the wrong place: "Krzysztof", "Kryzysztof" and "Krzystof" have different lengths and different fingerprints, yet share a 1-gram fingerprint because they use the same letters. Small n buys recall and costs precision.
Phonetic key collision covers a different failure, grouping values that sound alike through Metaphone3, Cologne, Daitch-Mokotoff or Beider-Morse. That is how "Catherine" and "Katharine" meet.
Nearest neighbor is the slow, broad option. Levenshtein counts single-character edits; PPM compares strings by how well they compress together. Both take a radius you tune, and both are quadratic: the manual notes that 3,000 strings mean 4.5 million pairwise comparisons. OpenRefine survives that by blocking, comparing only strings that share a substring of a given length. The default blocking size is 6 characters, and going below 3 or 4 explodes the runtime for very little extra recall.
A workable order of attack: fingerprint, then n-gram at 2 and 1, then Levenshtein with a small radius on the leftovers. Tick the clusters you trust, click Merge Selected and Re-Cluster, repeat. For the theory behind these method families, see clustering in data mining.
Clustering proposes. You approve. Every merge you accept becomes an operation in the history.
GREL, and the one example every tutorial copies wrong
For what clustering cannot do, OpenRefine has GREL, the General Refine Expression Language, still called the Google Refine Expression Language in older docs. Choose a column, then Edit cells and Transform, and write an expression that runs on every cell with value bound to it.
value.trim().toTitlecase()
value.replace(/[$£€,]/, "").toNumber()
value.split(" ")[0]
if(value.startsWith("**"), "", value)
value.match(/.*(\d{4}-\d{2}-\d{2}).*/)[0]The regex flavor is Java's, not PCRE, which matters the moment you paste a pattern from elsewhere. The syntax is covered separately in regular expressions for scraping.
Now the correction. Older versions of this article, and a great many tutorials, print the last line as value.match(/(\d{4})-(\d{2})-(\d{2})/)[0] and call it pulling a date out of text. It does neither. GREL's match() "Attempts to match the string s in its entirety" and returns an array of capturing groups. On a cell reading shipped 2026-08-10 the regex does not match the whole string, the function returns null, and indexing null throws. On a cell holding exactly 2026-08-10 it matches, and [0] hands you the first capturing group: 2026. You asked for a date and got a year. The manual states it outright: "A null is output when the entire regex does not match." Wrap the pattern in .* and put the whole date in one group.
Two more traps in that block:
toTitlecase()capitalizes each word and lowercases the rest, soIBMbecomesIbm. On a vendor column full of acronyms it destroys as much as it fixes.replace()cannot touch nulls, "as null is not a string." An empty cell and a null cell look identical in the grid and behave differently in every string function. Facet by blank first.
If GREL runs out, OpenRefine also accepts Python and Clojure. Read the Python offer carefully. It is Jython, bundled at version 2.7.4, which means Python 2 semantics: no f-strings, no Python 3 standard library, none of your pip packages.
Reconciliation: turning names into entities
This is the step that earns the phrase "data mining" rather than "data cleaning." Reconciliation matches free-text values against a structured knowledge base, so a string becomes an identified entity. Apple resolves to the company rather than the fruit, and once matched you can pull in industry, headquarters, or a stable identifier that survives the next rename.
Where the old tutorials used Freebase, OpenRefine ships with Wikidata reconciliation built in, and the protocol underneath is a published standard rather than a vendor feature. The Reconciliation Service API v0.2 is a Final Community Group Report of the W3C Entity Reconciliation Community Group, dated 10 April 2023, with a 1.0 draft in progress. The bundled Wikidata endpoint advertises versions 0.1 and 0.2 and a default type of Q35120. One thing to know: wikidata.reconci.link now redirects to wikidata-reconciliation.wmcloud.org, so an old hardcoded URL still works but no longer points where you think.
Select a column, choose Reconcile and Start reconciling, pick a service and an entity type, and OpenRefine scores candidates for you to approve. Adding another service means pasting a URL into Add Standard Service. The community test bench lists the public authorities: VIAF for names and the several hundred datasets the Organized Crime and Corruption Reporting Project exposes through Aleph.
Where the local-only promise stops
Every write-up about OpenRefine, this one included until now, says your data never leaves your machine. True of import, faceting, clustering and transforms. It stops being true in three places.
Reconciliation sends your column out. Matching against Wikidata means posting your values to a service on Wikimedia Cloud Services. Fine for public company names, wrong for customer records under an NDA.
Add column by fetching URLs is a crawler. It behaves like any other client hitting someone else's server. The default throttle is 5 seconds per row, and the manual recommends staying at 1,000 ms or above. It also lets you set User-Agent, Accept and Authorization headers, with a warning worth repeating: the credentials "get logged in your operation history in plain text." Extract that history to share a recipe and you have shared an API key.
Extensions are third-party code, running in the same process with the same access to your workspace, and their stated compatibility comes from the extension author rather than from the project.
The local-only claim is not a lie. It is a default that three specific features switch off.
Reusing your work, and what a recipe does not carry
Every operation lands in the Undo/Redo tab. Click Extract, tick the operations you want, and you get JSON. Paste it into another project's Apply box and the same steps run there. The process becomes the artifact, not the file.
Two limits decide how much of the work actually replays. The first is in the manual: "Not all operations can be extracted. Edits to a single cell, for example, can't be replicated." Anything you fixed by double-clicking exists only in that project.
The second is nowhere in the manual and matters more. A cluster merge is serialized as a mass-edit operation, and its payload is a literal lookup table (abridged):
{
"op": "core/mass-edit",
"columnName": "vendor",
"edits": [
{ "from": ["Apple Inc.", "APPLE, INC.", "apple inc"], "to": "Apple Inc." }
]
}The recipe carries the answers, not the method. Replay it on next month's file and every spelling you have seen before is fixed, while Apple Incorporated, appearing for the first time, sails through untouched. So apply the recipe first, then re-run clustering on what is left. That takes minutes rather than hours, because the recipe has absorbed the long tail from previous runs.
For unattended runs the project points at orcli, a Bash command-line interface at v0.4.2 from 31 July 2025 that drives a headless instance through the HTTP API. Check the alternatives before building on them. The widely cited openrefine-client was archived by its owner on 8 December 2024 and is read-only, its last release 0.3.10 from January 2021. The original Python client of the Google Refine era last shipped 0.2.1 in July 2011. The manual is direct: they are third-party code, and "the OpenRefine team does not maintain them and cannot guarantee that any of them work."
If you script against the server yourself, read the API reference's own health warning first: "This protocol is subject to change without warning at any time (and has in the past) and is not versioned." POST requests have needed a CSRF token since 3.3, and pinning the OpenRefine version in your image is the cheapest insurance.
Where OpenRefine stops
OpenRefine is desktop-scale, and the ceiling is memory rather than rows. With the heap raised it handles a few hundred thousand rows of ordinary scraped data. Push into millions of cells and you meet java.lang.OutOfMemoryError rather than a slow grid.
The architecture work meant to remove that ceiling exists and has not shipped. A 4.0 branch sits in the repository, and the project's architecture notes describe a rewritten history model with a lazy local runner that reads project data from disk on demand, "without the need for a large working memory." There is no 4.0 release, alpha or beta, and that branch still targets the Java range 3.10 targets while the main branch has moved on. When it lands is not knowable from outside.
For the jobs beyond the ceiling, versions current as of 10 August 2026:
- DuckDB 1.5.5, released 22 July 2026, queries CSV and Parquet directly with SQL and will group a few hundred million rows on a laptop. It is the shortest path from "too big for OpenRefine" to "still on one machine."
- pandas 3.0.5, also 22 July 2026, and Polars 1.43.2 from 1 August 2026 cover the scripted version of the transform section.
- Fuzzy matching at scale is its own discipline. Splink 4.0.16, released 11 March 2026, does probabilistic record linkage over millions of records. The older options, dedupe 3.0.3 and recordlinkage 0.16, have both been quiet for a while.
The other boundary is behavioral. OpenRefine is interactive by design, and its documentation says plainly that not every feature works without a human, naming clustering as the example. For a pipeline that runs nightly with nobody watching, port the transforms into code, as in the walkthrough for scraping and processing weather data with Python. Where this is a standing requirement rather than a weekend project, the halves split the same way: extraction is what a managed scraping service is for, and receiving analysis-ready files is what data as a service means.
A working order of operations
In sequence, on a real file:
- Import with the encoding set explicitly and cell-type parsing off, raising the heap first if the file clears the manual's thresholds.
- Text-facet every column you care about, sorted by count, and read the tail. That shows which fields your scraper is dropping.
- Cluster with fingerprint, then n-gram, then Levenshtein on the leftovers, approving merges by eye.
- Transform with GREL for what clustering cannot express, checking a preview row against the source page.
- Reconcile only the columns that need identifiers.
- Extract the operation history before closing the project, and store it next to the scraper that produced the file.
Step six is the one people skip, and the only one that pays you back next month. The cleaned file is worth an afternoon. The recipe is worth every afternoon after it.