Excel with XML-Map exports to XML with invalid decimal separator

1
votes

I have created an Excel Sheet, mapped it to an XSD schema and export the data to an XML File. This works fine except the fact, that the decimal values in the XML file have a wrong decimal separator.

I am from Europe and as many people are aware of, different than in the US, here we use the comma as decimal separator. So localization of the OS and Excel perfectly automatically use the comma.

But for example the entered number 7,5 in Excel results in a 7.5 value in the XML. There are no options to influence it. Neither in Excel, not in the export function or the XSD Definition. Since my regional settings should make sure comma is used, the Excel export seems to have a hardcoded conversion.

I use PowerBI to process the XML files and PowerBI expects a comma as decimal separator and here the numbers are totally screwed. The decimal . is ignored and so 1.7 gets interpreted as 75.

I hope this is a covering explanation.

Excel: enter image description here

XML: enter image description here

Power BI: enter image description here

A completely similar issue is explained very well in this Excel forum, but not answered.

How can I solve this?

1
xmlexcelxsdformattingdecimal-point

1 Answers

1
votes

Run a post-process on the XML file using XSLT. To format numbers with a comma, either use format-number() with <xsl:decimal-format decimal-separator=","/>, or just use translate(xxx, '.', ',')