OpenRefine — The Complete Guide: Cleaning Messy Data Tables Without Writing Code
OpenRefine is an open-source tool for sorting out messy data tables — equipment lists, bills of quantities or exports from different systems. It runs on your own computer and opens in the browser, but the data never leaves your machine.
OpenRefine
OpenRefine
Clean up messy data tables — duplicates, inconsistent names, splitting columns and conversions.
Get it from the vendorWhat you get
- Facets — instantly see every distinct value in a column and how often it appears.
- Value clustering — recognising 4” pipe, pipe 4 inch and PIPE 4 as the same item and merging them.
- Column editing — splitting, joining, trimming whitespace and converting types.
- Full history — every operation is recorded, can be undone, and can be replayed on a new file.
- Export — CSV, Excel and other formats.
Why not just Excel
In a spreadsheet you fix things by hand and forget what was fixed. In OpenRefine the list of steps is saved, so next month, when a new export arrives from the same system, you run the same clean-up with one click. It does not replace a spreadsheet for calculations — it prepares the data before it gets there.
Related tools
For more complex graphical data workflows — KNIME. For calculations on the clean table — LibreOffice. For full automation in code — Python.
KNIME Analytics Platform
KNIME
Data processing and analysis with visual workflows, no coding — cleaning, merging, statistics and machine learning.
Get it from the vendor
LibreOffice
The Document Foundation
A full office suite — documents, spreadsheets, presentations and drawings, compatible with Office files.
Get it from the vendor
Python
Python Software Foundation
The language behind most modern engineering computation, and its full ecosystem.
Get it from the vendor
R
R Foundation
The leading language for statistics and data analysis — quality control, experiment analysis, reliability and forecasting.
Get it from the vendorThe bottom line
OpenRefine saves hours of manual fixing and leaves a record of every change. Completely free, including for commercial use.