28.4.12

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.


Add leading zero

Here is a straight GREL expression to add a defined number of leading zero to any string (originally explained in the discussion list). The following expression turns the string into a four digit value:
"0000"[0,4-length(value)] + value 

Two parameters to update:
  • the number of 0 at the beginning
  • the final string length, here 4.

The expression for a three digits string will looks like: "000"[0,3-length(value)] + value 

Avoid this to happen again

On project creation
When you create your project, google refine offer you the option to detect the field format. This option is by default, untick it and all field will be in text format. You can then set manually the right format on a field by field basis. 


When splitting your column into several column

When splitting a cells into several column using the SplitByLenght function, google refine auto detect the format of then new field. To avoid this you need to untick the Guess cell type option.

Google refine may auto detect the cell type in other way, if so, please alert me and I'll update this article. Thanks

This post was

0 comments:

Post a Comment