C++/VB - Reformat text with a script

Asked By Bill

04-Feb-10 06:06 PM

Hi,

I was wondering if anyone could help me out -

I am trying to reformat the following data from a file called data.txt:
OR1387L3 Jan 20 2015  6:59PM
OR1363L3 Feb  4 2011  6:00PM

to remove the L3, drop the time, and reformat the date like this-

OR1387 01202015
OR1363 02042011

Any suggested are appreciated.

Thanks!

04-Feb-10 10:37 PM

You can use the FileSystemObject and the ReadLine Method to read and append
text files. More info can he found @
http://msdn.microsoft.com/en-us/library/hww8txat(VS.85).aspx

As for reformatting your text, once you have charged a variable with the
text to change you can use the Split function to break the string into a zero
based array.
The default delimiter of the Split function is the space character. If you
would like to see what elements have been split from the string the following
code will will show you. Remember this is a zero based array so in the
following example strSplit(0) = "Some" and strSplit(1) = "String"

strSplit = Split("Some String")
For i = 0 To UBound(strSplit)
MsgBox strSplit(i)
Next

Using this method we will reformat your string.

strToChange = "OR1387L3 Jan 20 2015  6:59PM"
'Use Split function to split the string into a zero based array.
strSplit = Split(strToChange)
strFileNumber = Left(strSplit(0),6) 'FileNumber
strDay = strSplit(2) 'Day
strYear = strSplit(3) 'Year
Select Case strSplit(1) 'Month
Case "Jan"
strMonth = "01"
Case "Feb"
strMonth = "02"
End Select
strNew = strFileNumber & " " & strMonth & strDay & strYear
MsgBox strNew

For Reference:
Here is the array of strSplit
strSplit(0) = "OR1387L3"
strSplit(1) = "Jan"
strSplit(2) = "20"
strSplit(3) = "2015"
strSplit(4) = " "    this is the extra space between "2015" and "6:59PM"
strSplit(5) = "6:59PM"

05-Feb-10 10:00 AM

When dealing with dates & times then you could make use of the various
date/time conversion functions that are built into VB Script, e.g. like so:
sLine = "OR1363L3  Feb 4   2011   6:00PM"
Do While InStr(sLine, "  ") > 0
Line = Replace(sLine, "  ", " ")
Loop
aLine = Split(sLine)
sKey  = Replace(aLine(0), "L3", "", 1, - 1, 1) & " "
dDate = CDate(aLine(1) & " " & aLine(2) & " " & aLine(3))
WScript.Echo sKey & Pad(Month(dDate)) & Pad(Day(dDate)) & Year(dDate)

Function Pad (n)
Pad = Right("0" & n, 2)
End Function

You now need to add the code that reads one line after the other from your
log file, then writes it to your output file. The File System Object has the
necessary methods for this. You will find detailed examples in the
downloadable help file script56.chm.

05-Feb-10 01:47 PM

This is perfect -

The only issue I ran into is that it seems to have a problem with OR1363L3
Feb  4 2011  6:00PM - it returns OR1363 024 instad of 02022011. Guessing it
does not like the single date digit.

Any ideas on how I can work around this?

Thanks again for your help!

05-Feb-10 01:49 PM

I tried running this and it gets stuck in a loop somewhere (and pegs out my
processor). It would definetly solve my issue with the single Feb day digit
though.

How can I see what is causing it to loop? All I did was past this code into a
a VBS file and run it to see what it returnned.

Thanks again for your help!

05-Feb-10 02:45 PM

I did not test the single date sorry!! However Richard's script will work
fine. In the Do While loop change
from:
Line = Replace(sLine, "  ", " ")

To:
sLine = Replace(sLine, "  ", " ")

Happy coding!

05-Feb-10 02:49 PM

Silly typo in my code, which LikeToCode picked up. As he said, the code
should look like so:

sLine = "OR1363L3  Feb 4   2011   6:00PM"
Do While InStr(sLine, "  ") > 0
sLine = Replace(sLine, "  ", " ")
Loop
aLine = Split(sLine)
sKey  = Replace(aLine(0), "L3", "", 1, - 1, 1) & " "
dDate = CDate(aLine(1) & " " & aLine(2) & " " & aLine(3))
WScript.Echo sKey & Pad(Month(dDate)) & Pad(Day(dDate)) & Year(dDate)

Function Pad (n)
Pad = Right("0" & n, 2)
End Function

05-Feb-10 02:51 PM

CORRECTION!!

However Pegasus's script will work fine.
Sorry Pegasus it must be all of the snow we are getting, cannot seem to read
straight!

05-Feb-10 03:25 PM

Thank you very much for this!

I am just having one last issue - in the first script you had the line:

strNew = strFileNumber & " " & strMonth & strDay & strYear

Which I was able to adapt to read the lines from my txt file.

But in pegasus' code it is:

WScript.Echo sKey & Pad(Month(dDate)) & Pad(Day(dDate)) & Year(dDate)

I tried changing the WScript.Echo to a vairable like this:

StrNew = sKey & Pad(Month(dDate)) & Pad(Day(dDate)) & Year(dDate)

But I got an error saying invalid syntax. How can I send the info to a
vairable instead of echo so i can read the lines from my txt file?

05-Feb-10 03:33 PM

In case it helps here is what i have that is giving me the syntax error-

Dim fso, inF, outF, sLine
Set fso = CreateObject("Scripting.FileSystemObject")
Set inF = fso.OpenTextFile("myfile.txt", 1)
Set outF = fso.OpenTextFile("outfile.txt", 2, True)

Do Until inF.AtEndOfStream
sLine = inF.ReadLine

Do While InStr(sLine, " ") > 0
Line = Replace(sLine, " ", " ")
Loop
aLine = Split(sLine)
sKey = Replace(aLine(0), "L3", "", 1, - 1, 1) & " "
dDate = CDate(aLine(1) & " " & aLine(2) & " " & aLine(3))
StrNew = sKey & Pad(Month(dDate)) & Pad(Day(dDate)) & Year(dDate)

Function Pad (n)
Pad = Right("0" & n, 2)
End Function

outF.WriteLine StrNew
Loop

05-Feb-10 03:40 PM

Bill, If I copy Pegasus's code below and change "wscript.echo" to "strNew"
and then on the next line add "MsgBox strNew" it all works as expected. Are
you spelling everything right? or could the error be from somewhere else in
your code?

05-Feb-10 04:02 PM

Take the Function out of the Do While Loop. The function need to be seperate.
you are also going to experience a infinite loop because your Instr and your
Replace code is looking for single space and replacing single space. Change
your code to
Instr(sLine, "  ") > 0  'double space
and
sLine = Replace(sLine, "  "," ")   'double space, single space

05-Feb-10 04:25 PM

You need to post the whole code and mark the line that raises the error.
Without this it is not possible to tell you what is wrong.

A general observation: When you copy code from respondents then you miss
most of the benefit unless you try to understand that code. Take my do-loop:
Did you reflect on why it is there and what it does? If so, why does your
code read
sLine = Replace(sLine, " ", " ")
when mine reads
sLine = Replace(sLine, "  ", " ")

Here is an explanation for its purpose:
I use the Split function to split your text line into an array. As
LikeToCode said, Split will use single spaces as a delimiter (by default).
Now your string sometimes has double spaces between words, sometimes single
spaces. Who knows, one day it might even have triple or quad spaces!
Repeatedly replacing double spaces with single spaces will "normalise" your
string so that when you drop out of the loop there will be one single space
between each word. Just what the doctor ordered and the Split function
expects!

Now you should realise why your line
sLine = Replace(sLine, " ", " ")
makes no sense.

05-Feb-10 05:26 PM

I very much appeciate both of you taking the time out of your day to help me,
and now I do understand what is going on - and I think I got it all figured
out.

Here is the code I ended up with, it works and wscript seems to terminate
afterwards so I think it is good to go - would you mind taking a look to see
if it looks OK?

Set fso = CreateObject("Scripting.FileSystemObject")
Set inF = fso.OpenTextFile("myfile.txt", 1)
Set outF = fso.OpenTextFile("outfile.txt", 2, True)

Function Pad (n)
Pad = Right("0" & n, 2)
End Function

Do Until inF.AtEndOfStream
sLine = inF.ReadLine
Do While InStr(sLine, "  ") > 0
sLine = Replace(sLine, "  ", " ")
aLine = Split(sLine)
sKey  = Replace(aLine(0), "L3", "", 1, - 1, 1) & " "
dDate = CDate(aLine(1) & " " & aLine(2) & " " & aLine(3))
StrNew= sKey & Pad(Month(dDate)) & Pad(Day(dDate)) & Year(dDate)
outF.WriteLine StrNew
Loop
Loop

Thanks again for your help!