Prepare SQL update where query in OpenRefine

Following the article regarding how to prepare SQL SELECT, INSERT INTO, DELETE query using OpenRefine, we will now see how the template function can be used to prepare UPDATE WHERE statement.

UPDATE WHERE statement are slightly different from a select or an insert statement since we want to define a different where clause for each record. Thus we cannot wrap all rows we want to update into a single query. We will need to write a separate statement for each record in our OpenRefine project and define the SET and WHERE conditions.

Prepare SQL SELECT, INSERT INTO, DELETE query using OpenRefine

I know that a lot of us export data from MySQL / SQL databases to clean them in OpenRefine before loading them back in their original database. Before, I was exporting my project to csv and loading the csv using some command utilities for MySQL, it worked by that was a painful process with a lot of details to pay attention to (encoding, field separator ...). But all this was was before I found a new way to use the template option of OpenRefine to prepare large select, update, insert or delete SQL statement

So instead of exporting to csv and importing through an other interface / tool like phpmyadmin you can use the template function of OpenRefine to preparethat will iterate through all the row of your project.

Padding left and right

Padding is the action of adding 0 to the left or the right of a text value until you reach a certain string lenght.

The padding function can be useful in OpenRefine if you lost leading zero while importing or transforming your data.

Of couse you can hack the example below to add letters or any other type of charactere.

padding left up to four digit
"0000"[0,4-value.length()] + value

padding right up to four digit
value + "0000"[0,4-value.length()]


source