If necessary, download OpenRefine (https://openrefine.org/) and launch.

## Step 1: Import your data

Upload your dataset into OpenRefine. You can import files from your computer in a variety of formats, including TSV, CSV, other *SV formats, Excel files (`.xls` and `.xlsx`), JSON, XML, RDF (as XML), and Google Data documents.  
Alternatively, you can paste your data directly using the **Clipboard** option.

## Step 2: Configure parsing options

Check that OpenRefine has parsed your data correctly before proceeding. In particular:

- Verify that column headers are correctly identified. If necessary, use **“Ignore first … lines”** or **“Parse next … lines as column headers”** to specify which rows should be treated as headers.
- Confirm that the character encoding is correct (UTF-8 usually works well).

## Step 3: Name your project, add tags if wanted and click on "Create project"


To understand the contents of your dataset and identify messy or inconsistent data, you can use **facets** and **filters** on individual columns.

1. Click the drop-down arrow next to the column header you want to analyse.
2. Select **Facet** and choose the appropriate facet type based on the data in the column:
   - **Text facet** (recommended for text columns):  
     Displays all unique text values in the column and shows how often each value occurs.
   - **Numeric facet** (for columns containing numbers):  
     Allows you to explore value ranges and detect outliers.
   - **Timeline facet** (for date columns):  
     Visualises dates along a timeline and helps identify missing or inconsistent date values.


Before making any changes, take time to understand your dataset.

- Use **facets** and **filters** on each column to see which values occur and how often.
- Look for common problems such as:
  - Different spellings of the same value
  - Inconsistent capitalisation
  - Extra spaces before or after text
  - Empty or missing cells
- Values that appear only once are often good candidates for closer inspection.

If you notice only a few incorrect values:

- Hover over a cell and click **Edit**.
- Correct the value manually and click **Apply**.
- This method is useful for isolated errors but should be avoided for cleaning large numbers of cells.


Many datasets contain unwanted spaces or inconsistent text formatting.

- Click the drop-down arrow next to the column you want to clean.
- Select **Edit cells → Common transforms**.
- Choose **Trim leading and trailing whitespace** to remove extra spaces.
- If needed, use **To lowercase** or **To uppercase** to make text consistent.

For more advanced or customised cleaning tasks, you can use **GREL (General Refine Expression Language)**. GREL allows you to transform values using simple expressions.

- Open the column menu and select **Edit cells → Transform**.
- In the expression box, write a GREL expression to modify the column values.
- The preview on the right shows how the transformation will affect your data before you apply it.

Common beginner-friendly examples include:
- `value.trim()` – removes extra spaces
- `value.toLowercase()` – converts text to lowercase
- `value.replace("old value","new value")` – replaces specific text
- `value.split(";")[0]` – extracts part of a value

> 💡 Tip: Always check the preview before clicking **OK**.

To learn more about GREL syntax and examples, see the official tutorial:  
👉 https://openrefine.org/docs/manual/grelfunctions


- Open a **Text facet** on the column.
- Click the **Cluster** button.
- Review the suggested clusters carefully.
- For each group, choose the correct or preferred form of the value.
- Click **Merge selected & re-cluster** to apply the changes.

> ⚠️ Always review clusters before merging to avoid combining values that should remain separate.

For columns containing numbers or dates:

- Use a **Numeric facet** to identify unusual values or outliers.
- Use a **Timeline facet** to check whether dates are missing, incomplete, or incorrectly formatted.
- If necessary, convert text to numbers or dates using **Edit cells → Transform**.


To identify empty cells:

- Open a **Facet → Customized facets → Facet by blank**.
- Decide how to handle missing values:
  - Leave them empty if the information is unavailable
  - Replace them with a standard value (e.g. “Unknown”)
  - Remove rows that do not contain enough information

If your dataset contains irrelevant information:

- Use filters to select rows you want to remove.
- Choose **Edit rows → Remove matching rows**.
- To delete a column, open the column menu and select **Edit column → Remove this column**.

OpenRefine keeps track of every action you perform.

- Open the **Undo / Redo** panel to review all cleaning steps.
- Use this panel to undo mistakes or understand how your data was transformed.
- This history can also be used to document your cleaning process for transparency and reproducibility.

Once you are satisfied with the results:

- Click **Export** in the top-right corner.
- Choose a suitable format (e.g. CSV or Excel).
- Save the cleaned dataset for further analysis or sharing.

Joëlle Weis

Michael Kurzmeier

Špela Antloga

DARIAH-EU

Community Engagement Working Group

This workflow provides a step-by-step guide to cleaning messy tabular data using OpenRefine. It was developed for a workshop organised by the DARIAH-EU Working Group on Community Engagement as part of the project Empowering Future Scholars: Supporting PhD Students’ Training Needs, funded by the DARIAH-EU Funding Scheme for Working Group Activities (2023–2025).

Edit actor

Other URLs relevant to the resource, e.g. ORCID, etc.

URL, ideally landing page, of the resource.

Person/group/body who helped create the resource.

Should be concise and raise interest in the entry. Allows <Link>Markdown</Link> content.

Other URLs relevant to the resource, e.g. code, DOI, etc.

Upload media

URL pointing to info about the access policy.

The activities you can do with the resource. See <Link></Link>.

Name of the conference where paper was given.

Describes the discipline covered by resource. See <Link></Link>.

Locations where the Resource is offered. See <Link></Link>.

URL to helpdesk for incidents & user requests.

Audience targeted by the resource. See <Link></Link>.

A particular published issue of a journal.

Concept or term related to MP entry. See <Link></Link>.

Language(s) in which a resource is available. See <Link></Link>.

Select license pertaining to resource. See <Link></Link>. If license is unknown, use the "Terms of use" property field to describe the usage terms.

Status of the Resource life-cycle. See <Link></Link>.

Mode of use for the resource. See <Link></Link>.

File format of the linked resource. See <Link></Link>.

Page #s of a resource, separate by commas.

Refine the resource's nature and function. See <Link></Link>.

Links to non-MP materials that are relevant.

Info about performance levels provider expected to deliver.

Standard used by the resource. See <Link></Link>.

Technology Readiness Level of the Resource. See <Link></Link>.

Resource user manual and documentation URL.

Version of the tool or service that is linked.

The volume of a journal or multivolume book.

Edit source

Name

If you are having trouble with the sign in process, please <ContactLink>contact the SSH Open Marketplace team</ContactLink>.

If you are having trouble with the sign up process, please <ContactLink>contact the SSH Open Marketplace team</ContactLink>.

Cleaning tabular data with OpenRefine

Workflow steps(13)

1 Download and Launch OpenRefine

2 Create project

3 Use facets and filters

4 Explore your data carefully

5 Make small manual corrections

6 Remove extra spaces and normalise text

7 Transform values using GREL

8 Find and merge similar values using clustering

9 Clean numeric and date columns

10 Deal with blank or missing values

11 Remove unnecessary rows or columns

12 Review and document your changes

13 Export your cleaned data