Reformat text with a script
in [VbScript]
|
Prev: Running ASP Page.
Next: SQL Version (Build) with WMI
From: Bill on 4 Feb 2010 18:06 Hi, I was wondering if anyone could help me out - I'm 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!
From: LikeToCode on 4 Feb 2010 22:37 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"
From: Pegasus [MVP] on 5 Feb 2010 10:00 "Bill" <Bill(a)discussions.microsoft.com> said this in news item news:F04630AD-A30C-4F1B-A768-597E2F9EF67D(a)microsoft.com... > Hi, > > I was wondering if anyone could help me out - > > I'm 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! 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.
From: Bill on 5 Feb 2010 13:47 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 doesn't like the single date digit. Any ideas on how I can work around this? Thanks again for your help! "LikeToCode" wrote: > 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" >
From: Bill on 5 Feb 2010 13:49
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's 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! "Pegasus [MVP]" wrote: > > > "Bill" <Bill(a)discussions.microsoft.com> said this in news item > news:F04630AD-A30C-4F1B-A768-597E2F9EF67D(a)microsoft.com... > > Hi, > > > > I was wondering if anyone could help me out - > > > > I'm 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! > > 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. > |