ISO 8601 datetime values not correctly loaded in EXCEL Sheet

[jveirasv]

Hello,

I have a MySQL database. I´m getting an XML when calling from an Excel macro a WebService that sends a Dataset serialized in XML format.

The problem is that XML dateTime values are coded using ISO 8601 standard within the XML UTC part included and it seems that Excel is unable to interpret it correctly.

This is an XML example with UTC data. Opening the XML file from Excel I'm getting a warning "Some data was imported as text" and the value written in the cell is, of course, text. If UTC information is removed (values after '+' symbol) then datetime field is correctly translated to Excel.

<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns="">
<vplan diffgr:id="vplan1" msdata:rowOrder="0">
<DBK>6628</DBK>
<COD>3000031</COD>
<FIRST_DATE>2012-10-26T19:40:31+02:00</FIRST_DATE>
<FIRST_TIME>PT9H</FIRST_TIME>
</vplan>
<vplan diffgr:id="vplan2" msdata:rowOrder="1">
<DBK>6733</DBK>
<COC>201109004730</COC>
<FIRST_DATE>2012-11-03T13:21:06+01:00</FIRST_DATE>
<FIRST_TIME>PT10H</FIRST_TIME>
</vplan>
</NewDataSet>
</diffgr:diffgram>

I don't know how to fix this. I need this to be done in an automatic way because I have to load a big number of registers from the DataBase. Code to translate to desired values is very time consuming. The same problem for TIME values (PT10H)


I tried to attach an XML Schema (using XMLImportXML function), but I don't really how to do it and if it could be the solution.

Please, give me some advice to solve this!!

Thanks!

Jesús

[Chippy]

Try this UDF to convert the date-times, which you can use in a cell formula:

Public Function CvtXMLTime(XMLtime As String) As Date

    '         1         2
    '1234567890123456789012345
    '2012-10-26T19:40:31+02:00
    
    CvtXMLTime = DateSerial(Mid(XMLtime, 1, 4), Mid(XMLtime, 6, 2), Mid(XMLtime, 9, 2)) + _
                 TimeValue(Mid(XMLtime, 12, 8)) + _
                 IIf(Mid(XMLtime, 20, 1) = "+", TimeValue(Mid(XMLtime, 21, 5) & ":00"), -TimeValue(Mid(XMLtime, 21, 5) & ":00"))
                 
End Function

Or call it from a procedure to convert the values in column E and put them in column H:

Sub Convert_XML_Times()
    
    Dim lastRow As Long, row As Long
    
    lastRow = Cells(Rows.Count, "E").End(xlUp).row
    For row = 2 To lastRow
        Cells(row, "H").Value = CvtXMLTime(Cells(row, "E").Value)
    Next
    
End Sub

[jveirasv]

Thank you Chippy for your reply.

I would like to avoid this kind of solutions because it means time to process going over all the registers. The use of Excel I'm doing to interact with MySQL DDBB is for a lot of views, and that means that some of them could have a few fields to be post-processed (time waiting for results)

I have divided the time consumed in three slots:

• time to recover information from the DDBB (WebService call and data travelling through internet)
• time to load data in the Sheet (currently with XMLimportXML, with the commented problem about some type of data not to be represented correctly as duration, datetime,...)
• time to postprocess data after loaded in dataSheet (apply format, fix time fields...)

I need to reduce second and third cases (client part). Some examples give me these values: t1(16 sec, 27%), t2(25 sec, 42%), t3(18 sec, 30%)

Any other ideas to improve this are welcomed!

Jesús

[skb123]

I am having the same issue when importing the xml file into excel. The Date format is ISO 8601 (like 2002-04-26T00:00:00-04:00).
When i import the xml file, excel gives xml parse error 'Some data is imported as text'.
In the Excel it exactly looks like this 2002-04-26T00:00:00-04:00. i can not change the format to mm/dd/yy as i guess excel does not recognise the date format.

Did you find any solution on this ?

[arlu1201]

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[snb]

=left(a1;10) + mid(a1;12;8)-right(a1;5)