Exporting XML from Excel and Keeping Date Format

Question

Many StackOverflow posts and other online forums have questions similar to this, but none of them really resolve the issue I'm having.

I'm exporting an XML file from Excel. The Excel file contains dates. In the XML file, however, those dates show up as integers--the date is made to be the nth day since 1/0/1900, I believe.

I'm using JavaScript to extract the data from the XML file to populate a table in a web page.

I suspect that a solution could come in two different forms: 1. A way to convert, in JavaScript, the integer to the date it represents. 2. A way to force the XML file to take a formatted date from the Excel file.

I'm clearly a novice with XML and JavaScript, so your assistance would be sincerely appreciated. Thanks.

Solution 1

The format Excel uses for the xml-export depends on the added XML-schema. This can be explicitly created and added or -- as I suspect in your case -- has been implicitly/ automatically generated by Excel.

In my example I have two colums, the first one has via XML-Schema to format date-time, the second is normal text and only for the representation in Excel (column format) a date.

When I export this as XML I get two seemingly completely different results:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<dataroot>
    <Tabelle11>
        <Title_1>2009-10-12T00:00:00.000</Title_1>
        <Title_2>40098</Title_2>
    </Tabelle11>
    <Tabelle11>
        <Title_1>2000-01-01T00:00:00.000</Title_1>
        <Title_2>36526</Title_2>
    </Tabelle11>
</dataroot>

When I check the XML-Schema (e.g. with "Debug.Print ActiveWorkbook.XmlMaps(1).Schemas(1).XML") I see the difference (only snippet, not the complete file):

<xsd:element name="Title_1" minOccurs="0" od:jetType="datetime" od:sqlSType="datetime" type="xsd:dateTime">
    <xsd:annotation>
        <xsd:appinfo>
            <od:fieldProperty name="Format" type="10" value="dd.mm.yyyy"/>
        </xsd:appinfo>
    </xsd:annotation>
</xsd:element>
<xsd:element name="Title_2" minOccurs="0" od:jetType="text" od:sqlSType="nvarchar">
    <xsd:annotation>
        <xsd:appinfo>
            <od:fieldProperty name="Format" type="10" value="@"/>
        </xsd:appinfo>
    </xsd:annotation>
    <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="255"/>
        </xsd:restriction>
    </xsd:simpleType>
</xsd:element>

The first column is in the schema of format datetime, the second is varchar. Again, you might not even be aware, how Excel handles your XML-data, since this schema is automatically generated (if I recall correctly in Excel 2003 there was a message indicating that an schema had benn generated for you)

Now how to acomplish this -- especially in an existing file: I would try to extract the XML-Schema (see above), save it as something.xsd, adapt the schema according to your needs and import this new Schema. Bummer is you have to set the mapping again (or try this: Excel 2007 XML Source Maps - Refreshing Schemas). Often I let MS Access generate my XML-Schema, i.e. generate the required table, insert some values and exprt the table as XML (ticking the option to add the schema).

I hope this general outline gives you an idea on how to proceed.

Regards

Andreas

Solution 2

It's actually pretty simple... if you're creating your own "schema" for example. Create an xml file, and say you have google analytics data to populate your full xml file with each row having the following three data points:

<date>1/1/2012</date>
<page>mywebsite.com</page>
<visits>100000</visits>

When you import this xml file as a data map schema for excel before exporting, you'll wind up the the integer you describe above. However, if you define this schema instead as:

<date>2012-01-01</date>
<page>mywebsite.com</page>
<visits>100000</visits>

Excel will show an additional grouping for the date element when you organize your xml on the sheet. Use the value (not the format()) as the column header, and your export will be the formatted date instead of the integer. You don't need to do any special .xsd extraction from excel or access!!

For an example of how to build your own schema, I found this video to be spot on: http://www.youtube.com/watch?v=9bat12gH3Qs