Create XML with Excel, date field formatting problem

Question

Create XML with Excel, date field formatting problem

Navigation

#1 by (1 votes)

1

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

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

I've tried some formatting combinations in Excel, but none of them worked. It even has a post here in stackoverflow, but solved my case.

Note: When I convert the range to xml, the msg: The data you are trying to map contains incompatible formatting with the format specified in the worksheet.

I'm using the Excel tools XML add-in.

Some configuration is 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>

Thanks in advance for the help.

excel xml

asked by anonymous 01.08.2014 / 18:03

1 answer

error in the comparison of binaries in C Java applets web scrap

score 1 · Answer 1

I found the solution, just need to resolve an issue, where the date is in English default and with "-" instead of "/" yyyy-mm-dd, if anyone knows how to handle this in Delphi, without having to do reading the entire string, replacing, etc. Or even in the xml map of Excel, that would be the most correct.

To resolve this issue:

I created an xml as the first post, but with the date in the right format dd / mm / yyyy, I imported the xml into Excel, and generated an xml schema (below), I just had to manually enter that field type was date (type="xsd: date"). Then just get the worksheet with the data and apply this scheme. When exporting, the field goes with date, only the question of formatting of the field remained, that was 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>