Creating XML for import into Excel, particularly dates

xml excel import

17,105

Solution 1

The trick is to have Excel infer the dates for you. In order for a date to be treated as such, format your date value as UTC (iso8601) and remove the "Z" and everything after that (excel doesn't support those). For example, turn this:

1999-05-31T13:20:00Z-05:00

to this:

1999-05-31T13:20:00

For more information on how Excel infers XML types check out MSDN

Solution 2

This worked for me without using xsd or whatever:

<StartDate>2009-07-14T01:02:35</StartDate>

Excel shows it according the actual regional settings:

07/14/2009 01:02

Solution 3

You could also create an Excel with a connection to the XML. On the sheet that contains the XML data, add extra columns for every datetime field in which you use a formula to create a datetime value out of a tekst value, eg =DATEVALUE([datetime_txt]) + TIMEVALUE([datetime_txt]). Excel will treat this as datetimes, even after refreshing your connection.

Solution 4

I would try this route: Create an XML Map data source like: (we'll call it "entriesMap.xsd")

<?xml version="1.0"?> 
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.w3schools.com" xmlns="http://www.w3schools.com" elementFormDefault="qualified">
    <xs:element name="entries">
      <xs:complexType>
        <xs:element name="entry">
            <xs:complexType>
                <xs:sequence>
                  <xs:element name="StartDate" type="xs:timeDate"/>
                  <xs:element name="Total" type="xs:integer"/>
                  <xs:element name="Connecting" type="xs:string"/>
                  <xs:element name="Disconnecting" type="xs:string"/>
                  <xs:element name="Queries" type="xs:string"/>
                </xs:sequence>
            </xs:complexType>
        </xs:element>
      </xs:complexType>
    </xs:element>  
</xs:schema>

In a new Excel worksheet:

Open the "XML Source" pane (Data-->XML-->XML Source)
Select the "XML Maps..." button in the XML Source pane
Select the "Add..." button
Select and open the XML Map file (change filetype *.xsd: our's is entriesMap.xsd)
Drag the map elements (StartDate, Total, etc) to their own cols or drag the top element all at once
Goto Data-->XML-->Import...
Select your XML data file

Not sure if I have the xsd file right, but I believe if you go this route it will work.

Solution 5

You can specify the worksheet format completely. Feels a bit ugly, the producer needing to understand so much of the destination.

<Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="12" ss:ExpandedRowCount="15" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="13.2">
   <Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="47.4" ss:Span="11"/>
   <Row ss:Index="4">
    <Cell ss:StyleID="s22"><Data ss:Type="DateTime">2009-04-20T00:00:00.000</Data></Cell>

View more solutions

17,105

Author by

Cylindric

Hello. #SOreadytohelp

Updated on June 07, 2022

Comments

Cylindric almost 2 years
Greetings.

I have a simple application that generates some performance-logging data, and I need the output to be accessible to Excel.

I create an XML document with the fields etc in it, and can open this in Excel.

The problem is, how do I coerce Excel to treat dates as dates? I've tried saving the date value as various formats, but Excel always treats it as text. If I click in the cell and hit Enter, it happily displays it as a date. Same when I do the "Text to Columns" thing.
```
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<entries>
  <entry>
    <StartDate>14/07/2009 01:02:35</StartDate>
    <Total>1084</Total>
    <Connecting>788</Connecting>
    <Disconnecting>0</Disconnecting>
    <Queries>98</Queries>
  </entry>
  <entry>
    <StartDate>14/07/2009 01:10:00</StartDate>
    <Total>1054</Total>
    <Connecting>228</Connecting>
    <Disconnecting>1</Disconnecting>
    <Queries>104</Queries>
  </entry>
</entries>
```
Cylindric almost 15 years

Aah, I see. I wasn't planning on creating actual an Excel XML document, seems a bit excessive. Is that necessary?
Cylindric almost 15 years

I can set the output format of the logger to whatever I like, what I don't want to have to do is add any formulae to Excel. Basically I was hoping I could just open it and Excel would recognise it. It recognises numbers after all :) My locale is set to UK, so the dd/mm/yyyy format kind of works - if I do add another column and use something like =MONTH(A2), it shows it correctly as "7", but the field is still text, so I can't just hit the "Short Date" button to format it. I'll just abandon Excel and create a XSLT for it or something.
devuxer almost 15 years

@Cylindric, Ahh, you're right. You may have already moved on at this point, but I think all you would need to do is have your logger format the date+time to match the proper XML schema DateTime simple data type ("YYYY-MM-DDThh:mm:ss") not one of the Excel preferred formats. I just tried out an example and it works. Excel infers the schema if you don't reference an XSD file in your XML, and it will only infer that an element contains a date if it's formatted as a proper DateTime according to XML schema specs. See: w3schools.com/Schema/schema_dtypes_date.asp
thedayofcondor almost 11 years

This is proper way - however XML mandates ISO8601 dates when using type="xs:timeDate" and Excel unfortunately does not support it - see office.microsoft.com/en-gb/excel-help/… . Come on M$!
Andez over 10 years

That makes me angry Microsoft Excel not supporting standard dateTime formats in XML.