Format in XML eksport from Excel 2010

[stenageo]

Hi

When using the Excel to XML export function a number with comma as decimal separator turns in to dot. Example: 23245,4 turns in to 23245.4 in the xml-file. This is a problem, since the system where the xml-file is imported do not allow dot, but demands a comma.

Any good ideas?

Best regards

Sten Olsen, Oslo Norway

 

[Excellister]

+1 on this question, I've looked all over for the solution on this... its impossible to find!

Next step for me is to write a VBA... Anyone have the solution to this?

 

[Excellister]

Re: Format in XML export from Excel 2010

Well, to explain a little better and how to recreate this error.. i thought I'd give it a second go.

When exporting a XML table from Excel, if a cell is formated as a number.. the export will not choose the default settings from regional settings in control panel. All numbers with decimals are exported with a period for the decimal separator instead of a comma like we use here in Iceland and many countries.

So to recreate this problem here it is:

In notepad, create and save as .xml a simple XML document. Here is my example:

<?xml version="1.0" encoding="iso-8859-1"?>
<ITEMS>
    <Product>
        <Product_id>007</Product_id>
        <Name>Apple</Name>
        <Price>107,56</Price>
    </Product>
</ITEMS>

Now choose import under development in a blank workbook and import the xml document and then format the 107,56 to a number then activate the cell and press enter. Now the number is a number, not text.

When this table is exported, it will show the following:
<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</items>

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ITEMS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Product>
        <Product_id>7</Product_id>
        <Name>Apple</Name>
        <Price>107.56</Price>
    </Product>
</ITEMS>

As we see, now the price has a period instead of a comma seperationg the decimals... this is not importable by my sql database.

Does anyone have an answer on how to choose the decimal seperator in XML export?

 

[Excellister]

After hours and hours of trying to figure this one out, I found a workaround that would not be as good as a proper solution but will help.

My tables all have formulas that retreive information from other tables. Using the "Text" function before the formulas in the cell actually tricks the export and the comma's stay

If someone has the proper solution to this, it would be greatly appreciated!