Import decimals from Excel: format always English notation?
Application Type
Traditional Web
Service Studio Version
11.10.20 (Build 41551)

Hi community members, 

I wonder if anyone has an answer to this problem. We're running a Dutch application. Dutch decimal numbers are noted as "10,3" instead of the English/international notation, as "10.3". 

We use an out-of-the-box Excel import to import enumeration values of fields to show data on our screens. Users can then select the value using a dropdown. However, users fill in details in a different system and we pull the data using a REST service. The data comes in correctly, using the Dutch notation "10,3". When opening the screen, the data is fetched and prefilled in the dropdown by matching these two values. Here's the problem: the dropdown shows values as "10.3" instead of "10,3". 

Therefore, there is a mismatch between the data retrieved ("10,3") and the data imported by the Excel ("10.3"). 

The Excel values are noted as "10,3". I even formatted the cells to be text in the Excel column, see screenshot:

After import, I check the database values, now they are noted as "10.3":

The import structure used as definition is a text. Nowhere is a decimal data type used for this attribute. So, I'm puzzled why it does convert it to a decimal somehow and convert it to the international. 

I'm wondering: could it be that in the import, an automatic conversion is done from the excel, that it interprets the value as decimal? And did anyone ever run into this problem?

Regards and thanks for an answer.

Michiel van Lokven

I now see your enumeration datatable is not a static entity. This is a bit confusing :)


I think the problem is in the excel: the value there is numeric. You should make the cells text before putting the value in there. The way Excel displays a value is always based on your preferences, but if its numeric, Outsystems will import the numeric value, not what you see in the cell.


For example: if you have a date in a cell in excell, and it says "di 2 januari 1992", the value will be 1992/01/02. In your case the value was 123,56 in the cell (format in screen) but its a numeric value and so the real value will be sent: 123.56.


So: 

either send in a text value by making the cell type text, or convert the value into the string you like.



Hello @Michiel van Lokven,

Could you please share a screenshot of the attribute in the database entity. just want to confirm if the data type of the attribute is decimal or text? For e.g. in the screenshot please capture the Data Type as shown in the sample image below:

 Thanks,

Regards,

AJ

Dear Michiel 

I have also tried and seems it is working fine. You Just need to be sure about the datatype you are using in EXCEL as well as in the Application. 

See below screenshot

Excel Sheet


Data after import -


Also Please Try to import once with the Bootstrap Method in that OutSystems automatically take the Datatype and save data automatically. You may find its working correctly. 


Thanks 

If i am correct you want to check the dutch decimal format with a decimal value you have in the database.

So first thing is: The static entity attribute should be decimal and will be stored like 1.35. 

Now the Rest will send a decimal value in dutch format, which is why you probably decided to use a text datatype for it. You will need to convert it into a decimal value by replacing the , with a .


After this you can do the proper validation. 

- If you do the check with text datatypes, you will also have the problem that 1,3 will not match 1,30.

- You can show the values in the dropdown to your liking with a format function.


Hope this helps you solving your problem.


Hi all,

Thanks for the answers so far. The structure and its data type definition is text. Do notice it's an import using an action as we have to update the data a few times per year:


It is then saved in an Enumeratie record:

I'll have to check if the data really is text in Excel. So both are data types text, which confuses me as OutSystems import seems to recognize it as decimal data type after importing.

I have been thinking about converting the data from the REST service, as Stefano suggests. But it brings new problems and feels like a workaround rather than a solution. 

Also in the Excel the fields are defined as text:

I will try importing it directly to see if that has a different effect.

Any other suggestions or help is welcome!

Regards,

Michiel van Lokven

I now see your enumeration datatable is not a static entity. This is a bit confusing :)


I think the problem is in the excel: the value there is numeric. You should make the cells text before putting the value in there. The way Excel displays a value is always based on your preferences, but if its numeric, Outsystems will import the numeric value, not what you see in the cell.


For example: if you have a date in a cell in excell, and it says "di 2 januari 1992", the value will be 1992/01/02. In your case the value was 123,56 in the cell (format in screen) but its a numeric value and so the real value will be sent: 123.56.


So: 

either send in a text value by making the cell type text, or convert the value into the string you like.



Hi Stefano,

Sorry for the confusion!

Converting the data to number in Excel works. So it was indeed a problem outside of the OutSystems domain. 

Regards,

Michiel

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.