[Video] Introduction to GREL

In partnership with the OKFN School of Data , we recorded last November an hour Skillshare on Refine GREL Language.  Always wonder how to get started with the Generic Refine Expression Language (GREL)?

Watch this one hour tutorial and learn the basic of the language along with simple expression. Further tutorial will be published to go through each functions presented in this video.

Read More

Field format change accidentally to Number and how to add leading 0

By inadvertence one can transform quickly a field containing number in a text format to number format. This mainly happen during the project creation (import) or when creating new column. This conversion to number can lead to a loss of data like leading 0. Here is how to get them back and avoid this to happen again.

Read More

Selecting a string within a cell using smartSplit

The function smartSplit is a variation on split function that allow you to split the cell content based on any string of character and then select the leg you want to work on. This function is very useful to extract or remove string within cells without creating multiple columns and then merging them back.

Read More

Starts or ends with a number

This is a quick and dirty tips to facet cells starting or ending with a number. Regex will be much cleaner for this but unfortunately the grel expression startsWith and endsWith does not support regex :-(

Read More

countif in google refine with facetCount

Countif is an expression in Excel that count every time a value appears in a determine zone of your spreadsheet. Google refine support the same function to count every time a value appears in a column.

Read More

remove " (quotation) mark

Hard time removing the " (quote sign) from your expression. Instead of quoting your quote mark with double quote, do it with simple like this :

Read More

Remove or replace a specific character in a column

You want to remove a space or a specific character from your column like the sign # before some number.

Read More

merge 2 columns that have both blank cells

New - March 2020 Update:

• OpenRefine 3.0, we have the coalesce() function:  which natively handles the null correctly. 

• OpenRefine 3.3 introduced a user interface that offers tons of flexibility. See tutorial

The basic function to merge or concatenate data from two different column is the following:

cells["col1"].value + " " + cells["col2"].value

Read More

Add a space to postal code (splitByLength and Merge function)

This short tips explains how to convert postal code store on 6 characters to 7 by adding a space after 3 digits. We will use splitByLength (see related video) and merge multiple column into one functions.

Read More

Compare values from two columns

To compare strings from 2 differents column and present the results in a third one, use the following expression:

Read More

Move data from a column to an other

Some data are messy and column can be a mix of different type of data. For example if you work on an address database, you might have a ZIP code column and a State column. And some ZIP code can be stored in the State Column.

Read More

Split cell content into multiple column, non fixed field length

I recently get a file to work on generated by crystal report and I had to deal with this format as no other were available. In my case, data were supposed to be split into 11 columns, in the original file there were all in 1, data were separated by a variable number of space. This post will present a process to split cell content when you have no markup. JSON code is provided for reference below.

Read More

How to merge records from different columns

New - March 2020 Update:

• OpenRefine 3.0, we have the coalesce() function:  which natively handles the null correctly. 

• OpenRefine 3.3 introduced a user interface that offers tons of flexibility. See tutorial

This tutorial has been merge with How to merge cells that have blank cells.

You can also refer to the following video tutorials:

Read More

Using "splitByLengths" in Google Refine

Learn how to use the "splitByLengths" function in Google Refine to split a single column into multiple columns based.

Read More