Openrefine: Transforming a number column to text adds a trailing .0

[ettorerizza]

Hi all,

tabular files often contain a numeric ID column. In Excel, this ID is sometimes stored erroneously as a number, not text. When importing this type of file into Open Refine and transform the ID into text, it often happens that Open Refine adds a .0 at the end. It can be deleted (value.replace(/.0$/, '').trim()), but I think this can be a problem for many users, since this ID column is often used for Vlookup / cell.cross().

[narendravardi]

@thadguidry can i work on this?

[thadguidry]

@narendravardi Go for it. Submit pull request when done.

[narendravardi]

@thadguidry any suggestions on where to make the changes?

I have gone through the classes EngineDependentMassCellOperation and TextTransformOperation based on the message printed on the gif file. But I couldn't find out. Please help me out! :)

[thadguidry]

@narendravardi it all starts here https://github.com/OpenRefine/OpenRefine/blob/master/main/webapp/modules/core/scripts/views/data-table/menu-edit-cells.js#L206

BUT you cannot just change that existing "to Text" operation because many folks already depend on its current functionality as its expected when you convert Java Numbers to Strings (and we don't want to throw away the original value that Apache POI found in the Excel cell either) https://docs.oracle.com/javase/tutorial/java/data/converting.html

Also, when we display a Number in the Cells in our datagrid, we are not displaying the .0 for whole numbers. That's a design choice we made long ago. And want to keep that. (and keep them green colored :) )

So...

I think we might want to have a new operation for this use case ?

@ettorerizza what do you think ? how about a "chomp Decimal Text" or "strip Fraction Text" or ?

UPDATE: Oh boy, I just thought of a wrinkle in this as well... not all users have a locale that uses a period or dot as the decimal separator... it all depends on their locale setting in their OS. It could be a comma if they are from Sweden :) So that proposed new operation will need to know their locale setting and that's pretty easy to find with a Java system call and then you'll know. https://stackoverflow.com/a/2123168/1100717

[thadguidry]

Hmm... I'm wondering if this issue is also showing itself in subtle ways even for cross() problems as in this guy's problem #1158

[thadguidry]

@narendravardi How's your progress coming on this issue ?

[thadguidry]

@narendravardi How's your progress on this ?

[jackyq2015]

I would say it is an expected behaviour. The original number has the 1 decimal. so the 14.0 will show as 14 when it's a number. But when you convert to text, it shows 14.0 to preserve the decimal. There is no need and also should not cut the trailing 0

[thadguidry]

@jackyq2015 this needs to be a display option then, I would say. So let's make an enhancement around the need to SHOW FULL PRECISION or somesuch....it has been asked for before on the mailing list in the past. Do you agree with adding a new display option, Jacky ?

[jackyq2015]

Agree. I found that issue number #1271 for full precision. will treat this one a duplicate of it.

[thadguidry]

@jackyq2015 cool. glad we agree