Question

Excel Importer: Thousand separator in number is causing problems

1

Hi all, I am running into a problem when using the Excel importer. When a value of 110000 is imported, Mendix receives the value as 110. This is due to the thousand separator ('.') used in Excel, making the value appear as 110.000 (cell value is still 110000). Removing the separator solves the problem (i.e. Mendix receives 110000). However, I would prefer that the final user does not have to remove separators. The format in Excel is 'number' and the attribute type in Mendix is integer. Language used is Dutch for both Excel and the Mendix project. Is there a solution for this problem?

asked 2016-06-23

Joost Ahrens

4 answers

Ronald Catersels · Answer 1 · 2016-06-23

You could solve this by making a custom microflow when importing this field. You are now probably using the formatInteger microflow on this field. This one removes everything behind the dot. By setting a breakpoint on this microflow you can check what you receive in this microflow so you can then do the conversion yourself.

Regards,

Ronald

Edwin Huitema · Answer 2 · 2016-06-24

Unfortunately this issue is stuffed away deep in the Java code, more specifically in the ValueParser class from replication.jar. Although it is patchable in the ExcelValueParser.java class, I've noticed that this only happens if you use the thousand separator alone. If you would use thousand and decimal separator (e.g. 110.000,00) it works ok.

I'd consider changing the format on the cells, rather than patching the code. You'll run into locale issues anyway when patching.

HTH, Edwin

Karthik S M · Answer 3 · 2016-06-28

Hi, Joost Ahrens Another good way to handle this is to create a custom parser and to convert it to string and u can trim the ,(commas) from the input string and store it as the expected value. Presuming the data type u have used in the excel template is an integer.

Jasper van der Hoek · Answer 4 · 2016-06-29

at some point I tried to cover all these scenario's in the module, the excel module is supposed to look at the display mask and destination attribute type.

If you would store it in a string, it should literally show what you see in excel, but if you store it in a number the display mask or parser shouldn't change the nr. This is something I tested extensively for en_US excel sheet, unfortunately the format of dutch excel sheets can be different and might influence the behavior negatively.

Are you sure you are using the latest excel version? If so can you share the excel sheet that causes this issue? We might be able to add that support in a future release.