Vb Script to convert a txt file to XML

Hi

Is there any code sample i can use to convert the attached text file to XML please?

The charaters within the file will alweays be on the same location. The only thing that may happen is some files may not include the LastDate, Time and SecondOcc (and assoicated values) contained.
wilko1000Asked:
Who is Participating?
 
aikimarkCommented:
Sub Q_28640567()
    Dim oFS, oTS
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim strXML
    Dim strFileData
    Dim oRE
    Dim oMatches
    Dim oM
    Dim lngSM
    Dim vFields
    Dim strWDTest
    Dim strFieldData
    Const cPathFile As String = "C:\users\aikimark\downloads\test2.txt"
    
    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile(cPathFile, ForReading, True, TristateFalse)
    strFileData = oTS.readall
    oTS.Close

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "W/D Tests\s+:\s+(\d+)"
    If oRE.test(strFileData) Then
        Set oMatches = oRE.Execute(strFileData)
        strWDTest = oMatches(0).submatches(0)
    End If
    
    oRE.Pattern = "\n(\d{4})\s+(.{1,21})\s+(\d\d\..*?:\d\d)\s+(\d+)(?:\s+(\d\d\..*?:\d\d)\s+(\d+))?"

    If oRE.test(strFileData) Then
        strXML = "<Log>" & vbCrLf
        vFields = Array("No", "Message", "FirstDateTime", "FirstOcc", "SecondDateTime", "SecondOcc")
        Set oMatches = oRE.Execute(strFileData)
        For Each oM In oMatches
            With oM
                strXML = strXML & "<MaintLog>" & vbCrLf
                strXML = strXML & "<WD_Test>" & strWDTest & "</WD_Test>" & vbCrLf
                For lngSM = 0 To .submatches.Count - 1
                    strFieldData = .submatches(lngSM)
                    Select Case vFields(lngSM)
                        Case "Message"
                            strFieldData = Trim(strFieldData)
                        Case "FirstDateTime", "SecondDateTime"
                            strFieldData = Replace(strFieldData, ".", "-")
                            If IsDate(strFieldData) Then
                                strFieldData = Format(CDate(strFieldData), "yyyy-mm-dd hh:nn:ss")
                            End If
                    End Select
                    strXML = strXML & "<" & vFields(lngSM) & ">" & strFieldData & "</" & vFields(lngSM) & ">" & vbCrLf
                Next
                strXML = strXML & "</MaintLog>" & vbCrLf
            End With
        Next
        strXML = strXML & "</Log>"
    End If
    Set oTS = oFS.OpenTextFile(Replace(cPathFile, ".txt", ".xml", Compare:=vbTextCompare), ForWriting, True, TristateFalse)
    oTS.write strXML
    oTS.Close
End Sub

Open in new window

0
 
aikimarkCommented:
there is no "attached file"
0
 
wilko1000Author Commented:
Sorry attached file now
Test1.txt
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
aikimarkCommented:
based on that text file, what should the (converted) XML file look like?
0
 
wilko1000Author Commented:
Ideally like this?

<Log>
      <MaintLog>
            <No>4142</No>
             <WD_Test>45</WD_Test>
             <FirstDateTime>2013-08-0312:29:14</FirstDateTime>
            <LastDateTime>2013-08-0312:29:14</LastDateTime>
            <Message>Test1</Message>
             <FirstOcc>2</FirstOcc>
            <SecondOcc>2</SecondOcc>
      </MaintLog>
<MaintLog>
            <No>4142</No>
            <WD_Test>45</WD_Test>
            <FirstDateTime>2013-08-0312:29:14</FirstDateTime>
            <LastDateTime>2013-08-0312:29:14</LastDateTime>
            <Message>Test2</Message>
            <FirstOcc>3</FirstOcc>
            <SecondOcc>3</SecondOcc>
</MaintLog>
</Log>

For each row
0
 
wilko1000Author Commented:
The only thing that might not be always in the incoming txt files is the Last Date, time and Second occurance (last 3 columns).
0
 
aikimarkCommented:
    Dim oFS, oTS
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Dim strXML
    Dim strFileData
    Dim oRE
    Dim oMatches
    Dim oM
    Dim lngSM
    Dim vFields
    Dim strWDTest
    Dim strFieldData
    
    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile("C:\users\aikimark\downloads\test1.txt", ForReading, True, TristateFalse)
    strFileData = oTS.readall
    oTS.Close

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "W/D Tests\s+:\s+(\d+)"
    If oRE.test(strFileData) Then
        Set oMatches = oRE.Execute(strFileData)
        strWDTest = oMatches(0).submatches(0)
    End If
    
    oRE.Pattern = "\n(\d{4})\s+(.{1,21})\s+(\d\d\..*?:\d\d)\s+(\d+)\s+(\d\d\..*?:\d\d)\s+(\d+)"

    If oRE.test(strFileData) Then
        strXML = "<Log>" & vbCrLf
        vFields = Array("No", "Message", "FirstDateTime", "FirstOcc", "SecondDateTime", "SecondOcc")
        Set oMatches = oRE.Execute(strFileData)
        For Each oM In oMatches
            With oM
                strXML = strXML & "<MaintLog>" & vbCrLf
                strXML = strXML & "<WD_Test>" & strWDTest & "</WD_Test>" & vbCrLf
                For lngSM = 0 To .submatches.Count - 1
                    strFieldData = .submatches(lngSM)
                    Select Case vFields(lngSM)
                        Case "Message"
                            strFieldData = Trim(strFieldData)
                        Case "FirstDateTime", "SecondDateTime"
                            strFieldData = Format(CDate(Replace(strFieldData, ".", "-")), "yyyy-mm-dd hh:nn:ss")
                    End Select
                    strXML = strXML & "<" & vFields(lngSM) & ">" & strFieldData & "</" & vFields(lngSM) & ">" & vbCrLf
                Next
                strXML = strXML & "</MaintLog>" & vbCrLf
            End With
        Next
        strXML = strXML & "</Log>"
    End If
    Set oTS = oFS.OpenTextFile("C:\users\aikimark\downloads\test1.xml", ForWriting, True, TristateFalse)
    oTS.write strXML
    oTS.Close

Open in new window

0
 
aikimarkCommented:
The only thing that might not be always in the incoming txt files is...
You should always post a representative sample of data.  The VBScript that I just posted will ignore lines that do not contain the second datetime.
0
 
wilko1000Author Commented:
Thanks aikimark, appreciate you taking the look.

Is it possible to make the script flexible to cater for files that might not have Seconddate, time and second occ and to allow the possiblity of more than 3 lines?

Example attached

The problem is the files coming in sometimes have Seconddate, time and second occ and upto 20 lines of text,
Test2.txt
0
 
aikimarkCommented:
So, the files will be EITHER format but NOT a mixture of both?

The number of detail lines doesn't matter.

Is this test2.txt file a representative sample of the data you need to convert?

What should the XML file look like with the test2.txt data?
0
 
wilko1000Author Commented:
Yes theres 2 types of file. So one file might contain the headers No, Message FirstDate, time, First Occ with the data listed underneath  but then another file might contain all the columns and associated data (No, Message FirstDate, time, First Occ, SecondDate, time, SecondOcc and data).

If a file does come in like test2.txt it will never contain haeaders with no data underneath.

In summary the txt file will either be:

(example test2.txt)
Headerline (Maintenace, WD test, Number of faults) followed by
No, Message FirstDate, time, First Occ with the data underneath each column
or
(example test1.txt)
Headerline (Maintenace, WD test, Number of faults) followed
No, Message FirstDate, time, First Occ, Lastdate, time, SecondOcc with the data underneath each column

test2.txt is an example of one of those files that do not contain the last 3 columns if you see what i mean.

The xml layout would be exactly the same just the tags for that data(lastdate, time, second occ) will be blank:
<Log>
      <MaintLog>
            <No>4142</No>
             <WD_Test>45</WD_Test>
             <FirstDateTime>2013-08-0312:29:14</FirstDateTime>
            <LastDateTime></LastDateTime> 'No data to display because Test2.txt did not have latdate and time
            <Message>Test1</Message>
             <FirstOcc>2</FirstOcc>
            <SecondOcc></SecondOcc>No data to display because Second Occ did nit have SecondOcc
      </MaintLog>
</Log>
0
 
wilko1000Author Commented:
Hi aikimark

i get an error on line 14 with that last script (attached error)
Capture.PNG
0
 
aikimarkCommented:
Sorry.  I was testing in a VBA environment.  The "As datatype" clause is not allowed in the VBScript environment.  The line should read:
Const cPathFile = "C:\users\aikimark\downloads\test2.txt"

Open in new window

0
 
wilko1000Author Commented:
aikiman your a legend, thanks for looking at this, this has been a ball ache for me but your sorted it, above and beyond
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.