Solved

Convert text file to xml using vbscript

Posted on 2015-02-04
47
1,086 Views
Last Modified: 2016-02-11
Hi

Does anyone know a quick way of converting a text file to html?

I have text a file thats layed out like this:

------------------------------------------------------------------------------------------  
07 Jul 13
General No : 45      Number of messages: 3
------------------------------------------------------------------------------------------  

No        Message                          Date       Time       Occ

4142    Text 1                            04.07.13   12:29:14     2
4142    Text 2                            04.07.13   12:29:14     3
4661    Text 3                            08.07.13   00:41:49     1

and want the VB Script to look in a location (folder) then read the txt file converting to an xml file so that it has a tag for date, General No, number of messages as the header then No. Message, Date, Time and Occ for each line (in this text file theres 3 lines).

Can anyone point me to a script or to a tutorial please?
0
Comment
Question by:wilko1000
  • 18
  • 17
  • 10
  • +1
47 Comments
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40590515
In the heading you are asking for conversion to XML, but in the first part of the question you state, "HTML".
Which one are you after?

I gather XML since you repeat that later

(For the record...to convert text to HTML you just add lines containing HTML tags as showing in this example to the start and end of your text file:
<html>
You text goes here.......
and here....
and here.....etc.
</html>

Open in new window

Very simple. You rename the text file (or take a copy and give it a new name) making sure the extension is ".html" or ",htm" (does not matter which).

As far as XML goes, I could write a short script for you which does everything you ask and will output to an XML file in probably the following format:

<MESSAGES>
       <MESSAGE>
             <NO>4142</NO>
             <TEXT>Text 1</TEXT>
             <DATE>04.07.13</DATE>
             <TIME>12:29:14</TIME>
             <OCC>2</OCC>
     </MESSAGE>
     <MESSAGE>
             <NO>4142</NO>
             <TEXT>Text 2</TEXT>
             <DATE>04.07.13</DATE>
             <TIME>12:29:14</TIME>
             <OCC>3</OCC>
     </MESSAGE>
       <MESSAGE>
             <NO>4661</NO>
             <TEXT>Text 3</TEXT>
             <DATE>08.07.13</DATE>
             <TIME>00:41:49</TIME>
             <OCC>1</OCC>
     <MESSAGE>
</MESSAGES>

It is just a matter of getting a file dialogue to pop up, the user selecting the text file in question, the code checking it is a text file in an acceptable format (in case they load a wrong text file), and then reading in each line, splitting the words from each line which starts with a four digit number using a space (" ")  as the splitter (split() function), noting that the first word is the "No" and the last word is the "Occ", the second last word is the "Times", the third last word is the "Date" and all other words between "No" and"Date" are the "Text".

Would you like the code in VBA, VB6 or VB.Net?
0
 

Author Comment

by:wilko1000
ID: 40590739
Wow thanks for taking a look and that appraoch for the XML parsing sounds good . And yes I did mean XML not HTML.

I do have to have the solution in VBScript (Extension .vbs file) which will be called by a scheduled task and if the file is in the wrong format put it in an error folder

How easy would it be to get the vb script to create an xml file in this schema:

<?xml version="1.0" encoding="UTF-8"?>
<ATA_DSE="E0037" version="E0037">
<Author authorID="E0037">
<EntryDate>07-Jul-13</EntryDate>
<Maintenance>Maintenance</Maintenance>
<Identifier>45</Identifier>
<GeneralNo>22</GeneralNo>
</Author>
<No>4142>
<Message>text 1</Message>
<EntryDateTime>04-07-13 12:29:14</EntryDateTime>
<Occ>2</Occ>
</No>
<No>4142>
<Message>Text 2</Message>
<EntryDateTime>04-07-13 12:29:14</EntryDateTime>
<Occ>3</Occ>
</No>
<No>4661>
<Message>Text 3</Message>
<EntryDateTime>08-07-13 00:41:49</EntryDateTime>
<Occ>1</Occ>
</No>
</ATA_DSE_Logbook>

Note the date and time in between the <No></No> node is now concatanated

Again thanks for taking a look
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40590892
@Wilko

* Your XML is not well formed.
* <No> should not be used in the manner in which you are using it.  Look at the craisin XML as a better representation.

Are you trying to use the No data as an attribute?
Example:
<MESSAGES>
       <MESSAGE No="4142">
             <TEXT>Text 1</TEXT>
             <DATE>04.07.13</DATE>
             <TIME>12:29:14</TIME>
             <OCC>2</OCC>
     </MESSAGE>
</MESSAGES>

Open in new window

0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40590935
Yes I agree that the schema you have put forward is unstructured. Either my approach or Aikimark's are suitable.

I will put together a VBS for you shortly.

I am working on it now...stand by..

Chris
0
 

Author Comment

by:wilko1000
ID: 40591067
Ok fantastc!

'No' is short for Message number it can be

<Message>
          <Message No>

          </Message No>
</Message>

Thats fine
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40591075
I have written something and just need to test it. I am also connected to the USA at the moment (Carbonite) whilethey do some work at restoring some files forme, so I may be held up for an hour or so. It is now 1.00am so I hope I can stay awake long enough.

The code seems to work but needs a little more testing....

Pls stand by

Cheers
Chris
0
 

Author Comment

by:wilko1000
ID: 40591120
No problem Chris
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40591199
I wasn't commenting on the name of the tag, "No".   I was commenting on your use of the tag as a parent to the other elements.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40591640
Is your header static?  If not, then where does some of the other data originate?
Example:
"E0037" for ATA_DSE and Author
"Maintenance"
"22"

From your earlier description, I expected the <GeneralNo> value to be 45.

What is the difference between the <GeneralNo> and the <Identifier> tags?
0
 

Author Comment

by:wilko1000
ID: 40592064
These will be headers and i put them in
<ATA_DSE="E0037" version="E0037">
 <Author authorID="E0037">

Forget the

Maintenance>Maintenance</Maintenance>

that is not needed and put in by mistake

These will always be in the incoming text file  but the values may be different i.e identifer maybe 45 today but 46 tomorrow
<Identifier>45</Identifier>
<GeneralNo>22</GeneralNo>

So incoming file will look like this:

07 Jul 13
 General No : 45      Number of messages: 3
 ------------------------------------------------------------------------------------------  

 No        Message                          Date       Time       Occ

 4142    Text 1                            04.07.13   12:29:14     2
 4142    Text 2                            04.07.13   12:29:14     3
 4661    Text 3                            08.07.13   00:41:49     1


And the out via VBscript would be something like this:

<?xml version="1.0" encoding="UTF-8"?>
 <ATA_DSE="E0037" version="E0037">
 <Author authorID="E0037">
 <EntryDate>07-Jul-13</EntryDate>
 <GeneralNo>45</GeneralNo>
<NumberOfMessages>3</NumberOfMessages>
 </Author>
<Message MessageID ="1">
    <No>4142>
    <Message>text 1</Message>
    <EntryDateTime>04-07-13 12:29:14</EntryDateTime>
    <Occ>2</Occ>
</Message>
<Message MessageID="2">
    <No>4142>
    <Message>Text 2</Message>
    <EntryDateTime>04-07-13 12:29:14</EntryDateTime>
    <Occ>3</Occ>
</Message>
<Message MessageID = "3">
    <No>4661>
    <Message>Text 3</Message>
    <EntryDateTime>08-07-13 00:41:49</EntryDateTime>
    <Occ>1</Occ>
</Message>
 </ATA_DSE_Logbook>
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40592156
where does the 22 come from?
0
 

Author Comment

by:wilko1000
ID: 40592291
22 was just an example number i supplied sorry for the confusion


To be exact it would be

Incoming text file:

Number Of Messages 3

Would be outputted as:

<NumberOfMessages>3</NumberOfMessages>
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40592296
OK, back on board.......stand by...
Chris
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40592310
@Christopher

Do you have an estimate of when you will post a solution?  I wanted to post one, but need to leave the house in an hour or so.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40592317
@wilko

That still isn't well formed XML.  You should validate your XML before you ask someone to create something.
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40592331
Is there always a space after General Number" and before the colon that follows?
0
 

Author Comment

by:wilko1000
ID: 40592347
Your right Aikimark, i should learn to use a validator!

try this

<?xml version="1.0" encoding="UTF-8"?>
  <ATA_DSE_Logbook operator="E0037">" version="E0037">">
  <Author authorID="E0037">
  <EntryDate>07-Jul-13</EntryDate>
 <GeneralNo>45</GeneralNo>
 <NumberOfMessages>3</NumberOfMessages>
  </Author>
 <Message MessageID ="1">
     <No>4142</No>
     <Message>text 1</Message>
     <EntryDateTime>04-07-13 12:29:14</EntryDateTime>
     <Occ>2</Occ>
 </Message>
 <Message MessageID="2">
     <No>4142</No>
     <Message>Text 2</Message>
     <EntryDateTime>04-07-13 12:29:14</EntryDateTime>
     <Occ>3</Occ>
 </Message>
 <Message MessageID = "3">
     <No>4661</No>
     <Message>Text 3</Message>
     <EntryDateTime>08-07-13 00:41:49</EntryDateTime>
     <Occ>1</Occ>
 </Message>
  </ATA_DSE_Logbook>
0
 

Author Comment

by:wilko1000
ID: 40592363
HI Chris yes theres a space.

I have attached the file for reference.
C--Users-wilkinsonc-Desktop-Example.txt
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40592492
why place a message tag inside a message tag?
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40592527
I think he meant "<Text>"

My code is almost finished.

Cheers
Chris
0
 

Author Comment

by:wilko1000
ID: 40592529
Resubmitted xml:

<?xml version="1.0" encoding="UTF-8"?>
  <ATA_DSE_Logbook operator="E0037">" version="E0037">">
  <Author authorID="E0037">
  <EntryDate>07-Jul-13</EntryDate>
 <GeneralNo>45</GeneralNo>
 <NumberOfMessages>3</NumberOfMessages>
  </Author>
 <Message MessageID ="1">
     <No>4142</No>
     <MessageDesc>text 1</MessageDesc>
     <EntryDateTime>04-07-13 12:29:14</EntryDateTime>
     <Occ>2</Occ>
 </Message>
 <Message MessageID="2">
     <No>4142</No>
     <MessageDesc>Text 2</MessageDesc>
     <EntryDateTime>04-07-13 12:29:14</EntryDateTime>
     <Occ>3</Occ>
 </Message>
 <Message MessageID = "3">
     <No>4661</No>
     <MessageDesc>Text 3</MessageDesc>
     <EntryDateTime>08-07-13 00:41:49</EntryDateTime>
     <Occ>1</Occ>
 </Message>
  </ATA_DSE_Logbook>
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40592584
Is the text always fixed length? Also, is it possible for the text to contain a date in the format "mm.dd.yy" or "mm/dd/yy"?
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40592635
OK...give this a try:

If you have any problems let me know.

Put the code in your VB Editor within Excel/Word/Access then run the macro. The macro is called "Text2XML" (original, huh?)

Of course you could perhaps eventually remove the "Success!" type message. Note that this macro runs one file at a time. If you want multiple files then it needs to run from another macro (which I could also write for you).

The tricky bit was the possible existence of Tabs and also the probable varing length of the "text" fields. Also the exitence of multiple spaces (between the fields) was a headache since the text could have valid multiple spaces.

Is this what you are after?

Cheers
Chris

Option Explicit

Public Sub TXT2XML()
Dim cLine As String
Dim nInFile As Integer
Dim nOutFile As Integer
Dim Token() As String
Dim Token2() As String
Dim nTokens As Integer
Dim nToken As Integer
Dim strInFile As String
Dim strOutFile As String
Dim bHeader As Boolean
Dim bMessages As Boolean
Dim bValid As Boolean
With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = "*.txt"
  .Title = "Please select desired text file"
  .AllowMultiSelect = False
  .InitialView = msoFileDialogViewDetails
  If .Show = -1 Then
    bValid = True
    strInFile = .SelectedItems(1)
  End If
End With
If bValid Then
    nInFile = FreeFile
    Open strInFile For Input As #nInFile
    'first a quick check it is a valid file
    bValid = False
    Do While Not EOF(nInFile)
      Line Input #nInFile, cLine
      cLine = Trim(cLine)
      If InStr(LCase(cLine), "general no") > 0 And InStr(LCase(cLine), "number of messages") > 0 Then
        bValid = True
        Exit Do
      End If
    Loop
    Close #nInFile
    If Not bValid Then
      MsgBox "Not a valid TXT file format for conversion"
      Exit Sub
    Else
      nInFile = FreeFile
      Open strInFile For Input As #nInFile
    End If
    Do While Not EOF(nInFile)
      Line Input #nInFile, cLine
      cLine = Trim(cLine)
      cLine = Replace(cLine, vbTab, " ")
      Token2 = Split(Trim(cLine), " ")
      ReDim Token(0)
      For nToken = 0 To UBound(Token2)
        If Len(Token2(nToken)) > 0 Then
          ReDim Preserve Token(UBound(Token) + 1)
          Token(UBound(Token)) = Token2(nToken)
        End If
      Next
      nTokens = UBound(Token)
      If nTokens > -1 Then
        If Not bHeader Then
          bHeader = True
          nOutFile = FreeFile
          strOutFile = Left(strInFile, Len(strInFile) - 4) & ".xml"
          Open strOutFile For Output As #nOutFile
          Print #nOutFile, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF - 8?" & Chr(34) & ">"
          Print #nOutFile, "<ATA_DSE=" & Chr(34) & "E0037" & Chr(34) & " Version= " & Chr(34) & "E0037" & Chr(34) & ">"
          Print #nOutFile, "<Author authorID=" & Chr(34) & "E0037" & Chr(34) & ">"
          If nTokens = 2 Then
            If Token(0) Like "##" And Token(1) Like "???" And Token(2) Like "##" Then
              Print #nOutFile, "<EntryDate>" & Token(0) & "-" & Token(1) & "-" & Token(2) & "</EntryDate>"
            End If
          End If
          Do While InStr(LCase(cLine), "number of messages:") = 0
            Line Input #nInFile, cLine
            cLine = Trim(cLine)
          Loop
          Token = Split(cLine, " ")
          Print #nOutFile, vbTab & "<GeneralNo>" & Token(3) & "</GeneralNo>"
          Print #nOutFile, vbTab & "<NumberOfMessages>" & Token(UBound(Token)) & "</NumberOfMessages>"
          Print #nOutFile, "</Author>"
        End If
        If UBound(Token) > 0 Then
          If Len(Token(1)) = 4 Then
            If Len(Token(nTokens)) = 1 Then
              If Token(nTokens - 2) Like "##?##?##" And Token(nTokens - 1) Like "##?##?##" Then
                'we have a valid line!
                If Not bMessages Then
                  bMessages = True
                  Print #nOutFile, "<Messages>"
                End If
                Print #nOutFile, vbTab & "<Message>"
                Print #nOutFile, vbTab & vbTab & "<No>" & Token(1) & "</No>"
                cLine = ""
                For nToken = 2 To nTokens - 3
                  cLine = cLine & " " & Token(nToken)
                Next
                Print #nOutFile, vbTab & vbTab & "<Text>" & cLine & "</Text>"
                Print #nOutFile, vbTab & vbTab & "<Date>" & Token(UBound(Token) - 2) & "</Date>"
                Print #nOutFile, vbTab & vbTab & "<Time>" & Token(UBound(Token) - 1) & "</Time>"
                Print #nOutFile, vbTab & vbTab & "<Occ>" & Token(UBound(Token)) & "</Occ>"
                Print #nOutFile, vbTab & "</Message>"
              End If
            End If
          End If
        End If
      End If
    Loop
    Print #nOutFile, "</Messages>"
    Close #nOutFile
    Close #nInFile
    MsgBox strOutFile & " created!"
  End If
End Sub

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

Expert Comment

by:aikimark
ID: 40592747
Here is my approach.  You pass the path\filename of the text file to the routine and it creates an XML file (extension/type) in the same folder with the same file name.
Sub Q_28610175(ByVal parmFilename As String)
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oSM As Object
    Dim lngSM As Long
    Dim strOutFilename As String
    Dim strFileData As String
    Dim vFields As Variant
    Dim lngMsg As Long
    Dim strTemp As String
    
    strOutFilename = Replace(parmFilename, ".txt", ".xml", , , vbTextCompare)
    Open parmFilename For Input As #1
    strFileData = Input(LOF(1), #1)
    Close
    Open strOutFilename For Output As #1
    Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #1, "<ATA_DSE_Logbook operator=""E0037"" version=""E0037"">"
    Print #1, "<Author authorID=""E0037"">"

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "(\d\d \w{3} \d\d)\s*General No : (\d+)\s+Number of messages: (\d+)"
    
    If oRE.test(strFileData) Then
        vFields = Array("EntryDate", "GeneralNo", "NumberOfMessages")
        Set oMatches = oRE.Execute(strFileData)
        Set oM = oMatches(0)
        With oM
            For lngSM = 0 To oM.submatches.Count - 1
                strTemp = oM.submatches(lngSM)
                If vFields(lngSM) = "EntryDate" Then
                    strTemp = Replace(strTemp, " ", "-")
                End If
                Print #1, "<" & vFields(lngSM) & ">" & strTemp & "</" & vFields(lngSM) & ">"
            Next
        End With
        Print #1, "</Author>"
        
    Else
        MsgBox "Header pattern does not match"
        Close
        Exit Sub
    End If
    
    oRE.Pattern = "(\d{4,})\s+(.*?)\s{2,}((?:[^ ]*)\s+(?:[^ ]*))\s+(\d+)"
    If oRE.test(strFileData) Then
        lngMsg = 1
        vFields = Array("No", "MessageDesc", "EntryDateTime", "Occ")
        Set oMatches = oRE.Execute(strFileData)
        For Each oM In oMatches
            Print #1, "<Message MessageID =""" & lngMsg & """>"
            lngMsg = lngMsg + 1
            For lngSM = 0 To oM.submatches.Count - 1
                strTemp = oM.submatches(lngSM)
                If vFields(lngSM) = "EntryDateTime" Then
                    strTemp = Replace(strTemp, ".", "-")
                End If
                Print #1, "<" & vFields(lngSM) & ">" & strTemp & "</" & vFields(lngSM) & ">"
            Next
            
            Print #1, "</Message>"
            
        Next
        
    Else
        MsgBox "Detail data pattern does not match"
        Close
        Exit Sub
    End If
    
    Print #1, "</ATA_DSE_Logbook>"
    Close
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40592764
My code would operate the same way if you want a file name passed to it rather than having the FileDialog.
I would call it from another macro, and could even pass an array of file names (like all files ending in ".txt" in a folder.

If you would prefer that then please let me know.

I avoided "regExp" to avoid the need for using imported libraries.

My code has actually already fulfilled the original request.

Cheers
Chris
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40592836
Since you started on your solution, I waited until you posted before I posted my code.
0
 

Author Comment

by:wilko1000
ID: 40593101
Really apreciate taking a look at this and i will try both solutions.

text (umder mesage column) may be of different lengths , the text column will be error messages probably going upto a maximum of 30 charaters but no less than 1 character

The date on the first line will always come in as 01<space>Jan<space>13 (DD MM YY)
The date in the Date column wll always be in the 01.01.13 format (DD.MM.YY)

The preferred why to run is calling a batch file called by the task manager if thats possible with the solutions? Finally, the number of lines (i.e. Lines under these columns [No   Message   Date       Time       Occ] maybe 3 lines one day as the attached files shows but maybe 10 lines the next day, will the solutions loop through or are they fixed just to check the 3 lines?

In the meantime i will test both
0
 

Author Comment

by:wilko1000
ID: 40593127
Just to calrify last statement the script has to be preferably a .vbs file which will be called by the task manager
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40593468
For VBS, change the Dim statements and replace the traditional I/O statements with FileSystemObjects.
Example:
    Dim oRE
    Dim oMatches 
    Dim oM 
    Dim oSM 
    Dim lngSM 
    Dim strOutFilename 
    Dim strFileData 
    Dim vFields 
    Dim lngMsg 
    Dim strTemp 

Open in new window

0
 

Author Comment

by:wilko1000
ID: 40593760
Renamed each Dim to the ones in your list above then changed the file name to .vbs but when i ran it i got the following error:

Script: Blah Blah.vbs
Line: 1
Char: 35
Error: Expected ')'
Code: 800A03EE
Source: Microsoft VBScript compliation error
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40594435
As long as each message is only on one line my text will have no problems (word wrap is OK, provided each line ends with a carriage return (Enter Key) and a line feed to the next line. Is it possible to supply some real (non-confidential) data, even made up stuff? (to use a technical term! LOL) :-)

I will adjust my code now to provide a "batch" type scenario run from task manager.

Will it always be just one file you are processing or will it be a group of files all present in a particular folder?
If the latter, I could set it up to procvess all files in folder "xyz" ending in ".txt"

If just one file, what will it's name be?"
0
 

Author Comment

by:wilko1000
ID: 40594644
I will supply a file yes no problem, what the scenario is i have a folder where inbound text files will land, so yes there maybe more than one file to process.

The structure of this text files will always be the same, the only thing that will change is the data in it. I have managed to get your script working in VBA and it does work with text files with various lengths of messages :).

But yes if it could be rigged up to be a vbs file (.vbs extension) that handles mulitple files that would be fantastic.


I will supply a real life file as requested (data is harmless).

There are some questions about the code content that i would like to ask but that can be later, i want to understand what the code does so i can have a ago myself
0
 

Author Comment

by:wilko1000
ID: 40594727
File attached...
M0406JUL131.txt
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40595572
OK...this seems to work.

Paste the code into a module and sav it as "txt2xml.vbs" (I also attach it for your convenience).

As a test place a text file in a folder and alter line 18 to point to that folder:

"sFolder = "H:\MyFolder"  (or whatever)

The output will have the same name as the text file but have an "xml" extension.

To call the code as a scheduled item you should use either of the two following commands:

"cscript //nologo c:\txt2XML.vbs  H:\MyFolder"  

     or

"wsscript //nologo c:\txt2XML.vbs  H:\MyFolder"  
(Alter the folder name from "c:\" and "H:" to suit your conditions).
 
Alternatively you can place one of those command in a batch file (".bat") and then schedule the batch file to run
(the downside for this is a "CMD" window will appear briefly at run time).

I used straight VB Script with no libraries since some machines may not have libraries on hand at run time.
Also the code is easier to understand if you are not very familiar with VBS (VB Script).

The downsode to using VB Script is that it is not strongly "typed". I used the setting "Option Explicit" to make
sure I was forced to defibe all variables (using the "Dim" command), but VBS does not allow you to state what TYPE of variable you are defining. This means it simply runs and depends on the programmer to make sure the correct data type is stored in each variable. If there is a mismatch it will only be noticed at "runtime" which can be fatal.
VBA and VB^ (and VB.Net) all check for mismatches and missing definitions during compiling so any errors are highlighted.

It took me a while to get used to the "freer" environment in VBS since I am a VB.Net programmer and am used to strict enforcement of declarations and typing.

There is still a minor problem in the final file being read, in that the header is written out again for some reason. I am still looking into fixing that. I am so tired at the moment that I will have to sleep on it (it is 2.30am and I have been working 5 hours on this).

Anyway, I hope this is what you are after. I will try and find the reason for the supurfluous "header" being tacjked on when I wake up tomorrow (today). :-)

If you want me to comment the code a lot more, I will do so to help you in any learning process.

Cheers from Australia! :-)

Chris

Option Explicit

Dim strOutput
Dim bHeader
Dim oFSO
Dim sFolder
Dim oFile
Dim TextIn

Set oFSO = CreateObject("Scripting.FileSystemObject")

If WScript.Arguments.Count > 0 then
  sFolder = WScript.Arguments(0)
Else
  sFolder = "c:\"
End If    
For Each oFile In oFSO.GetFolder(sFolder).Files
  bHeader = False 'tag the fact that the header for the next file is pending
  If UCase(oFSO.GetExtensionName(oFile.Name)) = "TXT" Then
    ProcessFiles oFSO, oFile
  End if
Next
Set oFSO = Nothing
bHeader = False

Sub ProcessFiles(FSO, File)
   Dim oFile2
   Dim cLines
   Dim nLine
   Dim cLine
   Dim Token2 
   Dim nToken
   Dim nTokens
   Dim bMessages
   Dim Token 
   Dim Token3
   Dim strFileName

  'first a quick check it is a file in acceptable format
   TextIn = fso.OpenTextFile(File.path).ReadAll
   If InStr(LCase(TextIn), "number of messages") = 0 Then
     'not a valid input file so skip this processing for this file
     Exit Sub
   End If
   oFile2 = FSO.OpenTextFile(File.path).ReadAll
   cLines = Split(oFile2, vbCrLf)
   For nLine = 0 To UBound(cLines) 
     cLine = Trim(cLines(nLine))
     cLine = Replace(cLine, vbTab, " ")
     If InStr(cLine," ") > 0 Then
       cLine = Trim(cLine)
       Token2 = Split(cLine, " ")
       ReDim Token(0)
       For nToken = 0 To UBound(Token2)
         If Len(Token2(nToken)) > 0 Then
           ReDim Preserve Token(UBound(Token) + 1)
           Token(UBound(Token)) = Token2(nToken)
         End If
       Next
       nTokens = UBound(Token)
       If nTokens > -1 Then
         If Not bHeader Then
           bHeader = True
           StoreOutput "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF - 8?" & Chr(34) & ">"
           StoreOutput "<ATA_DSE=" & Chr(34) & "E0037" & Chr(34) & " Version= " & Chr(34) & "E0037" & Chr(34) & ">" 
           StoreOutput "<Author authorID=" & Chr(34) & "E0037" & Chr(34) & ">" 
           If nTokens = 2 Then
             If Token(0) Like "##" And Token(1) Like "???" And Token(2) Like "##" Then
               StoreOutput "<EntryDate>" & Token(0) & "-" & Token(1) & "-" & Token(2) & "</EntryDate>"
             End If
           End If
           Do While InStr(LCase(cLine), "number of messages:") = 0
             If nLine < UBound(cLines) Then
               nLine = nLine +1
               cLine = Trim(cLines(nLine))
             Else
               Exit Do 'this line should never execute!
             End if  
           Loop
           If Len(Trim(cLine)) > 0 Then
             Token3 = Split(cLine, " ")
             StoreOutput vbTab & "<GeneralNo>" & Token3(3) & "</GeneralNo>"
             StoreOutput vbTab & "<NumberOfMessages>" & Token3(UBound(Token3)) & "</NumberOfMessages>"
             StoreOutput "</Author>"
           End if  
         End If
         If Len(Token(1)) = 4 Then
           If Len(Token(nTokens)) = 1 Then
             If DateOrTime(Token(nTokens - 2)) And DateOrTime(Token(nTokens - 1))Then
               'we have a valid line!
               If Not bMessages Then
                 bMessages = True
                 StoreOutput "<Messages>"
               End If
               StoreOutput vbTab & "<Message>"
               StoreOutput vbTab & vbTab & "<No>" & Token(1) & "</No>"
               cLine = ""
               For nToken = 2 To nTokens - 3
                 cLine = cLine & " " & Token(nToken)
               Next
               StoreOutput vbTab & vbTab & "<Text>" & cLine & "</Text>"
               StoreOutput vbTab & vbTab & "<Date>" & Token(UBound(Token) - 2) & "</Date>"
               StoreOutput vbTab & vbTab & "<Time>" & Token(UBound(Token) - 1) & "</Time>"
               StoreOutput vbTab & vbTab & "<Occ>" & Token(UBound(Token)) & "</Occ>"
               StoreOutput vbTab & "</Message>"
             End If
           End If
         End If
       End If
     End If
   Next
   StoreOutput "</Messages>"
   StoreOutput "</xml>"
   strFileName = oFile.ParentFolder.Path & Left(oFile.Name,Len(oFile.Name)-3) & "xml"
   Set File = FSO.createTextFile(strFileName, True)
   File.Write strOutput
   File.Close
   Set File = Nothing  
 End Sub
 
 Private Function DateOrTime(strText)
   Dim x
   DateOrTime = True
   If Len (strText) = 8 Then
     For x = 1 To 8
       If x <> 3 And x <> 6 Then
         If InStr("0123456789",Mid(strText,x,1)) = 0 Then
           DateOrTime = False
           Exit For
         End If  
       Else
         If InStr(":.",Mid(strText,x,1)) = 0 Then
           DateOrTime = False
           Exit For
         End If  
       End If
     Next
   End If  
 End Function
 
 Private Sub StoreOutput (strText)
   strOutput = strOutPut & strText & vbCrLf
 End Sub

Open in new window

Txt2XML.vbs
0
 

Author Comment

by:wilko1000
ID: 40596103
You are what we call here in the UK a legend...It would have taken me weeks of fumbling around to get this far...

initial test does nothing when i run the vbs file but that could be the file im using to test. Which file did you use to test, was any i attached to this thread? i will have another play later see if i can get it going

Thanks for the effort so far
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40596345
Thanks for your comments...it is my pleasure :-)

In answer to your question:  Yes, I did use the file you uploaded as well as a copy of the original data you supplied on line
((under ID 40592064 above).

There were seven text files in my root directory (goodness knows why), but only the two pertaining to this question were processed (as one would expect). Be aware that if you have a rogue text file containing the words "number of messages:" it will also be processed. (I assume this is an unlikely event).

Did you try running the codefirst with just the folder name stated in the code itself? (Line 15) Make sure your text file is in that folder. I just used the root directory (C:\")

To run it external to the scheduler double click on the vbs file within Windows Explorer (Press "Windows Key" + E to bring up Explorer).
Your machine should run it automatically.

If it runs OK with that methodology but not from within the System Task Scheduler, then it is the format of your command line in the scheduler which may be at fault. You can run any line in the scheduler by "right mouse clicking" on the listed scheduled item then select "Run". Don't forget to tack on the name of the Folder on your command line.

I think this is the best approach (passing a folder name to the procedure) rather than having to muck around with batch files to create a list of file names or anything like that.

I must say I had not actually tested the code I have supplied from within the scheduler itself, but I will do so now to verify that it works....stand by  :-)

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40596383
I just noticed when reviewing your earlier messages that you want any file not meeting the standars to go to a
seperate folder. That will be difficult, because unless you can guarantee that every file in the source folder (which is a txt file) must be processed (and is a valid candidate for processing) I could only "report" on files that do not have the words "number of messages:" in it.

To ensure the format is correct, we would have to test every word (apart from the "Text" fields to make sure they are of the correct wording and in the correct place within the text file (very difficult when the text can be varying lengths).

Can you live with the current plan, where every "candiidate" text file is processed? I could change the code if you like to send the "xml" files produced to a different folder. Also, perhaps consideration could be made to moving the processed files out to another folder also (so they are not reprocessed next time the scheduled task runs). What do you think of those ideas? (and if you like them where would those folders be, perhaps subfolders under the main folder?
                      e.g.            Main Data folder :   "H:\Txt2xml\Txt"
                             Processed files going to :   "H:\Txt2xml\Processed"
                                    Produced XML files :   "H:\Txt2xml\xml"

All very handy for backing up with all the data under the one folder "H:\Txtwxml"  :-)

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40596612
Assuming you are OK with my proposals:
             1. Have a  "Processed" folder under the main folder which contains the text files already processed                  
             2. Have an  "xml" folder under the main folder which contains the produced xml files
             3. Have a "txt" folder under the main folder which contains the text files yet to be processed

The file structure will look like (withing say "C:\Program Files (x86)\Txt2XML"):
Folder containg main VBS file with 3 sub folders
then the following altered code will meet that need. I have also attached it.




Option Explicit

Dim strOutput
Dim bHeader
Dim oFSO
Dim sFolder
Dim oFile
Dim TextIn

Set oFSO = CreateObject("Scripting.FileSystemObject")

If WScript.Arguments.Count > 0 then
  sFolder = WScript.Arguments(0)
Else
  sFolder = "c:\txt2xml\txt"
End If    
For Each oFile In oFSO.GetFolder(sFolder).Files
  bHeader = False 'tag the fact that the header for the next file is pending
  If UCase(oFSO.GetExtensionName(oFile.Name)) = "TXT" Then
    ProcessFiles oFSO, oFile
  End if
Next
Set oFSO = Nothing
bHeader = False

Sub ProcessFiles(FSO, File)
   Dim oFile2
   Dim cLines
   Dim nLine
   Dim cLine
   Dim Token2 
   Dim nToken
   Dim nTokens
   Dim bMessages
   Dim Token 
   Dim Token3
   Dim strFileName
   'Dim rs 

  'first a quick check it is a file in acceptable format
   TextIn = fso.OpenTextFile(File.path).ReadAll
   If InStr(LCase(TextIn), "number of messages") = 0 Then
     'not a valid input file so skip this processing for this file
     Exit Sub
   End If
   oFile2 = FSO.OpenTextFile(File.path).ReadAll
   cLines = Split(oFile2, vbCrLf)
   For nLine = 0 To UBound(cLines) 
     cLine = Trim(cLines(nLine))
     cLine = Replace(cLine, vbTab, " ")
     If InStr(cLine," ") > 0 Then
       cLine = Trim(cLine)
       Token2 = Split(cLine, " ")
       ReDim Token(0)
       For nToken = 0 To UBound(Token2)
         If Len(Token2(nToken)) > 0 Then
           ReDim Preserve Token(UBound(Token) + 1)
           Token(UBound(Token)) = Token2(nToken)
         End If
       Next
       nTokens = UBound(Token)
       If nTokens > -1 Then
         If Not bHeader Then
           bHeader = True
           StoreOutput "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF - 8?" & Chr(34) & ">"
           StoreOutput "<ATA_DSE=" & Chr(34) & "E0037" & Chr(34) & " Version= " & Chr(34) & "E0037" & Chr(34) & ">" 
           StoreOutput "<Author authorID=" & Chr(34) & "E0037" & Chr(34) & ">" 
           If nTokens = 2 Then
             If Token(0) Like "##" And Token(1) Like "???" And Token(2) Like "##" Then
               StoreOutput "<EntryDate>" & Token(0) & "-" & Token(1) & "-" & Token(2) & "</EntryDate>"
             End If
           End If
           Do While InStr(LCase(cLine), "number of messages:") = 0
             If nLine < UBound(cLines) Then
               nLine = nLine +1
               cLine = Trim(cLines(nLine))
             Else
               Exit Do 'this line should never execute!
             End if  
           Loop
           If Len(Trim(cLine)) > 0 Then
             Token3 = Split(cLine, " ")
             StoreOutput vbTab & "<GeneralNo>" & Token3(3) & "</GeneralNo>"
             StoreOutput vbTab & "<NumberOfMessages>" & Token3(UBound(Token3)) & "</NumberOfMessages>"
             StoreOutput "</Author>"
           End if  
         End If
         If Len(Token(1)) = 4 Then
           If Len(Token(nTokens)) = 1 Then
             If DateOrTime(Token(nTokens - 2)) And DateOrTime(Token(nTokens - 1))Then
               'we have a valid line!
               If Not bMessages Then
                 bMessages = True
                 StoreOutput "<Messages>"
               End If
               StoreOutput vbTab & "<Message>"
               StoreOutput vbTab & vbTab & "<No>" & Token(1) & "</No>"
               cLine = ""
               For nToken = 2 To nTokens - 3
                 cLine = cLine & " " & Token(nToken)
               Next
               StoreOutput vbTab & vbTab & "<Text>" & cLine & "</Text>"
               StoreOutput vbTab & vbTab & "<Date>" & Token(UBound(Token) - 2) & "</Date>"
               StoreOutput vbTab & vbTab & "<Time>" & Token(UBound(Token) - 1) & "</Time>"
               StoreOutput vbTab & vbTab & "<Occ>" & Token(UBound(Token)) & "</Occ>"
               StoreOutput vbTab & "</Message>"
             End If
           End If
         End If
       End If
     End If
   Next
   StoreOutput "</Messages>"
   StoreOutput "</xml>"
   Dim strPath 
   Dim strName
   strPath = oFile.ParentFolder.ParentFolder.Path
   strName = oFile.Name
   If FSO.FolderExists(strPath & "\xml\") Then
     strFileName = strPath & "\xml\" & Left(oFile.Name,Len(oFile.Name)-3) & "xml"
   Else
     strFileName = strPath & "\" & Left(oFile.Name,Len(oFile.Name)-3) & "xml"     
   End If  
   Set File = FSO.createTextFile(strFileName, True) 'will overwrite file if it already exists
   File.Write strOutput
   File.Close   
   If FSO.FolderExists(strPath & "\xml\") Then
     FSO.MoveFile strFileName, strPath & "\xml\"
   End If
   If FSO.FolderExists(strPath & "\Processed\") Then
     strFileName = strPath & "\Txt\" & strName
     FSO.MoveFile strFileName, strPath & "\Processed\"
   End If


   Set File = Nothing  
 End Sub
 
 Private Function DateOrTime(strText)
   Dim x
   DateOrTime = True
   If Len (strText) = 8 Then
     For x = 1 To 8
       If x <> 3 And x <> 6 Then
         If InStr("0123456789",Mid(strText,x,1)) = 0 Then
           DateOrTime = False
           Exit For
         End If  
       Else
         If InStr(":.",Mid(strText,x,1)) = 0 Then
           DateOrTime = False
           Exit For
         End If  
       End If
     Next
   End If  
 End Function
 
 Private Sub StoreOutput (strText)
   strOutput = strOutPut & strText & vbCrLf
 End Sub

Open in new window

Txt2XML.vbs
0
 

Author Comment

by:wilko1000
ID: 40596618
absolutely happy with that proposal and understand the bit about not being able to check for errors and thats no problem.

Initial test still does nothing when i run the VBS script despite setting up folders and having a txt file in but its probably me being thick so will get the files from this thread and test those.

Again appreciate the effort you have put in, not much i know but maximum points coming your way cheers
0
 

Author Comment

by:wilko1000
ID: 40596686
Ah they are being processed! its dumping them (in XML format) on the desktop and hence why i  could not see them. I will double check my folder structure
0
 

Author Comment

by:wilko1000
ID: 40596691
Got it going now :) And is moving and converting sweet.

One thing noticed was that theres no <EntryDate> in the xml file.

I noticed this line there seems to be a condition which reads the first line:

If nTokens = 2 Then
             If Token(0) Like "##" And Token(1) Like "???" And Token(2) Like "##" Then
               StoreOutput "<EntryDate>" & Token(0) & "-" & Token(1) & "-" & Token(2) & "</EntryDate>"
             End If

Does this mean if the date (first line in txt file) is not in a certain format then it wont create the tag?

And finally how would i get the vb script to concatinate the <Date> and <Time> so that its reads
From text :

Date
01.01.2015
Time
13:40:10
#Output

<EntryDateTime>2015-01-0113:40:10</EntryDateTime>

The latter is not important so please dont go out the way for that
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40596702
OK...I successfullyran it from the scheduler after setting up the schedule as follows (assuming the folder structure just shown):

Create basic scheduling Task
Adjust Settings on "General" TabAdjust settings on "Trigger" TabClick "Edit" button on "Actions" TabComplete settings on Actions Box which appearsAdjust more settings on Settings Tab then click "OK" to acceept all changes
If you are watching the schedulerwhen it runs, do not worry that it appears to saty on "Working" in trhe schedule panel (does not appear to stop). That is just becaise the scheduler sometimes does not refresh its screen when a task
finishes (depending on the type of task).

See how you go!  :-)

Cheers
Chris
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40596710
Oops! Our messages "crossed in the mail"  LOL

Yes, the date must be in the format "12 Jan 15" (two digits followed by space followed by three characters followed by space folowed by final two digits). I can add extra conditions if the date could be in different formats...just let me know.

Is it possible the date could be adiffwerent format?

As far as the concatenation goes the code should be amended as shown follows:
1.   Remove these two lines (around line 102/103)

               StoreOutput vbTab & vbTab & "<Date>" & Token(UBound(Token) - 2) & "</Date>"
               StoreOutput vbTab & vbTab & "<Time>" & Token(UBound(Token) - 1) & "</Time>"
2, Replace witth one lines:
         StoreOutput vbTab & vbTab & "<EntryDateTime>" & Token(UBound(Token) - 2) & " " & _
                                                                                                Token(UBound(Token) - 1) & "</EntryDateTime>"

Notice the "continuation" underscore at the end of the first line (for long lines to make it easier to read) and also the
space (" ") after the date) so the date and time do not join together (unless you want it like that!) :-)

Does that fix that problem?

My only remaining concern is the extra "Header" repeatd at the end of the last "XML" file produced.  still have to work that out. If there are NO "rogue" files (file which do not pertain to the correct txt format) then this problam may not happen. I put some rogue files in on purpose just to see the effect. I am still trying to find the fix for that.

Stand by....
0
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 500 total points
ID: 40596875
OK...That has now been fixed (I forgot to clear the Output text after each file was processed, so it was repeating the text in the next file).

A couple of other minor fixes to logic (such as when trying to move the text file to the "\Processed" folder resulted in an error if the file already existed). I had to replace "Move" with "copy" to destination, then "Delete" from source.

It all looks error free now (but of course users usually find something one has overlooked).

I repeat in full the final code and attach it.

Let me know if this now supplies the process you are after.

Cheers
Chris

Option Explicit

Dim strOutput
Dim oFSO
Dim sFolder
Dim oFile
Dim TextIn

Set oFSO = CreateObject("Scripting.FileSystemObject")

If WScript.Arguments.Count > 0 then
  sFolder = WScript.Arguments(0)
Else
  sFolder = "c:\Program Files (x86)\txt2xml\txt"  'an existing folder on the PC
End If 

For Each oFile In oFSO.GetFolder(sFolder).Files
  'bHeader = False 'tag the fact that the header for the next file is pending
  If UCase(oFSO.GetExtensionName(oFile.Name)) = "TXT" Then
    ProcessFiles oFSO, oFile
  End if
Next

Set oFSO = Nothing

Sub ProcessFiles(FSO, File)
   Dim oFile2
   Dim cLines
   Dim nLine
   Dim cLine
   Dim Token2 
   Dim nToken
   Dim nTokens
   Dim bMessages
   Dim Token 
   Dim Token3
   Dim strFileName
   Dim strPath 
   Dim strName
   Dim bHeader

  'first a quick check it is a file in acceptable format
   TextIn = fso.OpenTextFile(File.path).ReadAll
   If InStr(LCase(TextIn), "number of messages") = 0 Then
     'not a valid input file so skip this processing for this file
     Exit Sub
   End If
   oFile2 = FSO.OpenTextFile(File.path).ReadAll
   cLines = Split(oFile2, vbCrLf)
   For nLine = 0 To UBound(cLines) 
     cLine = Trim(cLines(nLine))
     cLine = Replace(cLine, vbTab, " ")
     If InStr(cLine," ") > 0 Then
       cLine = Trim(cLine)
       Token2 = Split(cLine, " ")
       ReDim Token(0)
       For nToken = 0 To UBound(Token2)
         If Len(Token2(nToken)) > 0 Then
           ReDim Preserve Token(UBound(Token) + 1)
           Token(UBound(Token)) = Token2(nToken)
         End If
       Next
       nTokens = UBound(Token)
       If nTokens > -1 Then
         If Not bHeader Then
           bHeader = True
           StoreOutput "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "UTF - 8?" & Chr(34) & ">"
           StoreOutput "<ATA_DSE=" & Chr(34) & "E0037" & Chr(34) & " Version= " & Chr(34) & "E0037" & Chr(34) & ">" 
           StoreOutput "<Author authorID=" & Chr(34) & "E0037" & Chr(34) & ">" 
           If nTokens = 2 Then
             If Token(0) Like "##" And Token(1) Like "???" And Token(2) Like "##" Then
               StoreOutput "<EntryDate>" & Token(0) & "-" & Token(1) & "-" & Token(2) & "</EntryDate>"
             End If
           End If
           Do While (InStr(LCase(cLine), "number of messages:") = 0) And (InStr(LCase(cLine), "number of messages :") = 0)
             If nLine < UBound(cLines) Then
               nLine = nLine +1
               cLine = Trim(cLines(nLine))
             Else
               Exit Do 'this line should never execute!
             End if  
           Loop
           If Len(Trim(cLine)) > 0 Then
             Token3 = Split(cLine, " ")
             StoreOutput vbTab & "<GeneralNo>" & Token3(3) & "</GeneralNo>"
             StoreOutput vbTab & "<NumberOfMessages>" & Token3(UBound(Token3)) & "</NumberOfMessages>"
             StoreOutput "</Author>"
           End if  
         End If
         If Len(Token(1)) = 4 Then
           If Len(Token(nTokens)) = 1 Then
             If DateOrTime(Token(nTokens - 2)) And DateOrTime(Token(nTokens - 1))Then
               'we have a valid line!
               If Not bMessages Then
                 bMessages = True
                 StoreOutput "<Messages>"
               End If
               StoreOutput vbTab & "<Message>"
               StoreOutput vbTab & vbTab & "<No>" & Token(1) & "</No>"
               cLine = ""
               For nToken = 2 To nTokens - 3
                 cLine = cLine & " " & Token(nToken)
               Next
               StoreOutput vbTab & vbTab & "<Text>" & cLine & "</Text>"
               'StoreOutput vbTab & vbTab & "<Date>" & Token(UBound(Token) - 2) & "</Date>"
               'StoreOutput vbTab & vbTab & "<Time>" & Token(UBound(Token) - 1) & "</Time>"
               StoreOutput vbTab & vbTab & "<EntryDateTime>" & Token(UBound(Token) - 2) & " " & Token(UBound(Token) - 1) & "</EntryDateTime>"
               StoreOutput vbTab & vbTab & "<Occ>" & Token(UBound(Token)) & "</Occ>"
               StoreOutput vbTab & "</Message>"
             End If
           End If
         End If
       End If
     End If
   Next
   StoreOutput "</Messages>"
   StoreOutput "</xml>"
   strPath = oFile.ParentFolder.ParentFolder.Path
   strName = oFile.Name
   If FSO.FolderExists(strPath & "\xml\") Then
     strFileName = strPath & "\xml\" & Left(oFile.Name,Len(oFile.Name)-3) & "xml"
   Else
     strFileName = strPath & "\" & Left(oFile.Name,Len(oFile.Name)-3) & "xml"     
   End If  
   Set File = FSO.createTextFile(strFileName, True) 'will overwrite file if it already exists
   File.Write strOutput
   File.Close
   'move the text file to "Processed" by first copying across and then deleting from "Txt" Folder
   '(VBS cannot move a file if it already exists in the destination folder, so we must copy then delete.)
   strFileName = strPath & "\txt\" & strName 
   If FSO.FolderExists(strPath & "\Processed\") Then
     fso.CopyFile strFileName, strPath & "\Processed\",True
     fso.DeleteFile strFileName
   End If
   strOutput = ""  'clear the output text ready for next file    
   Set File = Nothing  
 End Sub
 
 Private Function DateOrTime(strText)
   Dim x
   DateOrTime = True
   If Len (strText) = 8 Then
     For x = 1 To 8
       If x <> 3 And x <> 6 Then
         If InStr("0123456789",Mid(strText,x,1)) = 0 Then
           DateOrTime = False
           Exit For
         End If  
       Else
         If InStr(":.",Mid(strText,x,1)) = 0 Then
           DateOrTime = False
           Exit For
         End If  
       End If
     Next
   End If  
 End Function
 
 Private Sub StoreOutput (strText)
   strOutput = strOutPut & strText & vbCrLf
 End Sub

Open in new window

Txt2XML.vbs
0
 

Author Closing Comment

by:wilko1000
ID: 40597345
Response was fantastic, above and beyond, thanks for having the patience and going through that in detail
0
 

Expert Comment

by:Systemofuniverse
ID: 40967139
Hi,
I need a similar script like this. I've tried to modift it but could not make it.
My txt file is like this:

POLONYA LİGİ : RUCH CHORZOW  19:00  PODBESKİDZİE BİELSKO
ALMANYA KUPASI : AALEN  19:30  NURNBERG , ARMİNİA BİELEFELD  19:30  HERTHA BERLİN , OSNABRUCK  19:30  RB LEİPZİG
ÇEK CUMHURİYETİ LİGİ : JİHLAVA  20:00  JABLONEC , SLOVAN LİBEREC  20:00  DUKLA PRAG
MEKSİKA LİGİ : PUEBLA  3 - 2  PUMAS UNAM
İSVEÇ LİGİ : KALMAR  2 - 0  GIF SUNDSVALL

I need to convert it to xml file. this is a live match score text file and it is renewing every 2 seconds.
i.e. for first line,  POLONYA LİGİ : is the name of the league, RUCH CHORZOW  is team name, 19:00  time (or score), PODBESKİDZİE BİELSKO second team name.
Confusing part is team names-score are separated with , (comma) and spose to be same tag.
Is it possible to do it with vbscript ?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now