Link to home
Start Free TrialLog in
Avatar of Victor  Charles
Victor CharlesFlag for United States of America

asked on

Help with converting data format in xml file using VB.NET

Hi,

How do I convert the following data format <Date>1986-10-20T00:00:00-04:00</Date> to <Date>MM/DD/YYYY</Date> using VB.NET?

Thanks,

Victor
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Try..
one of these... <<Replace Now with your datefield>>

Dim newDate = FormatDateTime(Now, DateFormat.ShortDate)

Dim newDate = Now.ToShortDateString()
Avatar of YZlat
you could try:

Dim dt As DateTime = DateTime.ParseExact("1986-10-20T00:00:00", "yyyy-MM-dd HH:mm:ss", Nothing)

Dim result As String = dt.ToString("MM/dd/yyyy")

Open in new window

You can do like this, taking the timezone into consideration as well while ignoring local date separators:

Dim utcDate As String
Dim shortDate As String
utcDate = "1986-10-20T00:00:00-04:00"
shortDate = DateTime.Parse(utcDate).ToUniversalTime().Date.ToString("MM'/'dd'/'yyyy")

' Will return: 10/20/1986

Open in new window

/gustav
Avatar of Victor  Charles

ASKER

Hi,

I need to clarify further, How do i modify the date in an xml file and save to a new file after making the change?

For example if file1.xml contains:

<Root>
<Table>
<ID>1</ID>
<Name>xxx</Name:
<Date>1986-10-20T00:00:00-04:00</Date>
</Table>
</Root>

How do I create file2.xml with the new date format?

File2.xml

<Root>
<Table>
<ID>1</ID>
<Name>xxx</Name:
<Date>MM/DD/YYYY</Date>
</Table>
</Root>

Actual project has many more records, this is just an example.

Thanks,

Victor
For a clean solution, you would have to deserialize from the xml file, modify, and serialize again to xml. It is not that difficult in .Net.

If the file is simple, you may be able to parse it line by line to look up each occurrence of <Date>...</Date>, replace the content, and write each line to a new file.

/gustav
Something like that should work:

 Dim dt As DateTime
        Dim result As String
        Dim xmlDoc As New XmlDocument
        xmlDoc.Load("C:\Test\myfile.xml")
        Dim xnodeList As XmlNodeList = xmlDoc.SelectNodes("//Table/Date")

        For Each xnode In xnodeList
            If xnode IsNot Nothing Then
                dt = FormatDateTime(xnode.InnerText, DateFormat.ShortDate)
                result = dt.ToString("MM/dd/yyyy")
                xnode.InnerText = String.Format("{0:MM/dd/yyyy}", result)
            End If
        Next

xmlDoc.Save("file2.xml")

Open in new window

Hi,

I am using the code below, but getting error message: Conversion from string "" to type 'Date' is not valid.

on line:   dt = FormatDateTime(xnode.InnerText, DateFormat.ShortDate)

How do I fix this error?

code:

 Dim dt As DateTime
        Dim result As String
        Dim xmlDoc As New XmlDocument
        xmlDoc.Load(Application.StartupPath & "\LinkSearch.xml")
        Dim xnodeList As XmlNodeList = xmlDoc.SelectNodes("//MyDataTable/Date")

        For Each xnode In xnodeList
            If xnode IsNot Nothing Then
                dt = FormatDateTime(xnode.InnerText, DateFormat.ShortDate)
                result = dt.ToString("MM/dd/yyyy")
                xnode.InnerText = String.Format("{0:MM/dd/yyyy}", result)
            End If
        Next

        xmlDoc.Save(Application.StartupPath & "\LinkSearchnew")

Thanks,

Victor
I am using the code below, but getting error message: Conversion from string "" to type 'Date' is not valid.

Yes from XML you must be getting empty strings and when you are trying to convert that to date you get that error. You must check the value before conversion like below-. This we called input data validation. In this case USE IsDate() Function.

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Dim dat As String = "2016/01/01"
        Dim dt As Date

        If IsDate(dat) Then
            dt = FormatDateTime(dat, DateFormat.ShortDate)
        Else
            dt = FormatDateTime("1900/01/01", DateFormat.ShortDate)
        End If

    End Sub

Open in new window


Output

If the Input is 2016/01/01 then the Output is #1/1/2016#
If the Input is "" then the Output is #1/1/1900#

Open in new window


Note - What I am doing above is when the input string is not date then - 1900/01/01. and if the input string is valid the we format the input string directly.

Hope it helps !!
Hi,

How do you modify the code below using your latest approach?

Dim dt As DateTime
         Dim result As String
         Dim xmlDoc As New XmlDocument
         xmlDoc.Load(Application.StartupPath & "\LinkSearch.xml")
         Dim xnodeList As XmlNodeList = xmlDoc.SelectNodes("//MyDataTable/Date")

         For Each xnode In xnodeList
             If xnode IsNot Nothing Then
                 dt = FormatDateTime(xnode.InnerText, DateFormat.ShortDate)
                 result = dt.ToString("MM/dd/yyyy")
                 xnode.InnerText = String.Format("{0:MM/dd/yyyy}", result)
             End If
         Next

         xmlDoc.Save(Application.StartupPath & "\LinkSearchnew")


Thanks,

Victor
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank  You.