0

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

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.

  • Hi Peter, we would probably have UK English rather than US English, or Australian English. The Settings in Windows are Australian English - it looks like mapinfo reads those. – LeasMaps Jun 13 '17 at 2:24
  • Internally MapInfo Pro is always using the US English number format. The regional settings are only used when displaying number, for example in the browser window and in dialogs. In this case here, MapInfo basically gets confused by the formatted numbers. That's why you need to deformat the number using DeformatNumber$(). I have added a more simple method in the answer above. – Peter Horsbøll Møller Jun 13 '17 at 11:05
  • Thanks Peter - I've passed this onto our developer and she'll give it a go. – LeasMaps Jun 15 '17 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.