Create XML with Excel, Date field formatting problem

Question

Create XML with Excel, Date field formatting problem

I have an Excel spreadsheet, generated by SAP with a date field. I need to convert this spreadsheet to XML.

I do the XML mapping by Excel, but I can not make it export the date field as a date, always appears the equivalent number as below, where the date is 06/06/2014, and in the XML appears 41796.

I've tried a few formatting combinations in Excel, but none has advanced. Even has a post right here on stackoverflow, but you solved my case.

Note: when I convert range to xml, MSG appears: The data that vc is trying to map contains an incompetent formatting with the format specified in the spreadsheet.

I'm using Excel'S own XML tools add-in.

Is some configuration missing. Is there any way to specify the data type in the XML map?

     <Row>
        <Documento_SD>3208510</Documento_SD>
        <Denominacao>DISJUNTOR DWA800N-630-3</Denominacao>
        <Nome_1>A B RODRIGUES</Nome_1>
        <Data_de_remessa>41796</Data_de_remessa>
     </Row>

Thank you for your help.

1

excel xml

Author: user3771516, 2014-08-01

Source

1 answers

score 1 · Answer 1

I found the solution, just need to solve a question, where the date is leaving in English default and with " - "instead of" / " YYYY-mm-dd, if someone knows how to handle this in Delphi, without having to read the entire string, substitution, etc. Or even in Excel xml map, which would be the most correct.

To solve this question:

I created an xml as first post, but with the date in the right format dd / mm / YYYY, imported the xml in Excel, and generated an xml schema (below), I just needed to manually enter that the field type was date (type= "xsd: date"). Then just take the spreadsheet with the data and apply this scheme. When exporting, the field goes with date, only remained the issue of formatting the field, which remained with the fields spaced with - and in the English standard.

<?xml version='1.0' encoding='UTF-16'?>
<!-- Created from XmlMap.Name: Root_Mapa -->
<!-- XmlMap.DataBinding.SourceUrl:  -->
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="">
<xsd:element nillable="true" name="Root">
    <xsd:complexType>
        <xsd:sequence minOccurs="0">
            <xsd:element minOccurs="0" maxOccurs="unbounded" 
 nillable="true" name="Row" form="unqualified">
                <xsd:complexType>
                    <xsd:sequence minOccurs="0">
                        <xsd:element minOccurs="0" nillable="true" 
 type="xsd:integer" name="Documento_SD" form="unqualified"/>
                        <xsd:element minOccurs="0" nillable="true" 
 type="xsd:string" name="Denomina__o" form="unqualified"/>
                        <xsd:element minOccurs="0" nillable="true" type="xsd:string" 
 name="Nome_1" form="unqualified"/>
                        <xsd:element minOccurs="0" nillable="true" type="xsd:date" 
 name="Data_de_remessa" form="unqualified"/>
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
        </xsd:sequence>
    </xsd:complexType>
 </xsd:element>
 </xsd:schema>

XML output:

 <Row>
    <Documento_SD>3249301</Documento_SD>
    <Denomina__o>FENIX INDUSTRIA E PRESTACAO DE</Denomina__o>
    <Nome_1>CHAVE PDWCA08-5V40</Nome_1>
    <Data_de_remessa>2014-07-04</Data_de_remessa>
</Row>