Creating XML for import into Excel, particularly dates

Question

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>

Answer 1

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>

Answer 2

I see your problem. Your date format is dd/mm/yyyy. If you change it to mm/dd/yyyy, Excel will automatically recognize it as a date.

So, one solution would be to change how your performance data gets logged.

The other would be to create a new column in Excel that uses a formula like this:

=VALUE(MID(A2,4,2)&"/"&LEFT(A2,2)&RIGHT(A2,LEN(A2)-5))
(where A2 is the cell containing the date text)

This formula physically rearranges the date so that the month is before the day, then applies the VALUE function to it to convert it into a numerical date+time.

If there could be only one "day" digit (e.g., 1/07/2009 01:02:35), you would need to check for that with an if statement, like this:

=VALUE(IF(FIND("/",A2)=2,MID(A2,3,2)&"/"&LEFT(A2,1)&RIGHT(LEN(A2)-4),MID(A2,4,2)&"/"&LEFT(A2,2)&RIGHT(A2,LEN(A2)-5)))

Hope that helps.

One other thought: if this is just going to be used on your machine, you might be able to change your Windows default date/time format. I think this influences how Excel interprets the date.

Answer 3

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:

1. Open the "XML Source" pane (Data-->XML-->XML Source)
2. Select the "XML Maps..." button in the XML Source pane
3. Select the "Add..." button
4. Select and open the XML Map file (change filetype *.xsd: our's is entriesMap.xsd)
5. Drag the map elements (StartDate, Total, etc) to their own cols or drag the top element all at once
6. Goto Data-->XML-->Import...
7. 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.