Changing format of column from character to decimal or float in Mapinfo

Question

We have some Mapbasic software that updates character fields in existing mapinfo tables to decimal or float. The character fields are mostly numeric but some values are N/A to indicate not applicable which is more correct for us that 0.

This is working fine for values under 1,000 (note that the format has a comma in it) but for values greater than 1,000 it is treating the comma as a decimal point I think. Any numbers greater than this are altered to 1 2 etc WE are using commands as follows:

    Alter Table "tmp_astext" ( modify LV Float ) 
    Alter Table "tmp_astext" ( modify LV Decimal(16,4) ) 

Is there any way we can stop this behaviour without having to make the users remember to change the formats when creating the files initially (they are created from excel). I've tried using the command

        Set Format Number "9,999.9"

in the Mapbasic window but it doesn't seem to help

Answer 1

This might depend on your regional settings.

English/US regional settings are using . as decimal separator and , as digit grouping separator. Their numbers would look like this: 1,234,567.90

Many European countries are using the separators in the opposite way, , as decimal separator and . as digit grouping separator. Their numbers would look like this: 1.234.567,90.

But you are right, it does seem that merely changing the Char column into a Numerical column has a bad sideeffect on the numbers.

I would suggest doing this in a couple of steps instead to make sure value are correctly converted:

1. Add a number numerical column to the table
2. Update the numerical column with a deformatted version of the number
3. Commit changes to the table
4. Remove the char column
5. Rename the numerical column

These are the MapBasic statements:

Alter Table "tmp_astext" ( Add LV_numerical Float)
Update tmp_astext Set LV_numerical = DeformatNumber$(LV)
Commit Table tmp_astext Interactive
Alter Table "tmp_astext" ( Drop LV) 
Alter Table "tmp_astext" ( Rename LV_numerical LV)

Another more simple appoach is:

Update tmp_astext Set LV = DeformatNumber$(LV)
Commit Table tmp_astext Interactive
Alter Table "tmp_astext" ( modify LV Float ) 

If you want to end out with a Decimal column, you can do that directly after instead of first changing it to Float and then to Decimal.