Solved

A date format changing script.

Posted on 2012-04-04
15
97 Views
Last Modified: 2013-09-28
Hi everyone,

Ive run into another stumper.

I have a text file with a huge amount of lines that look like this:

Tag1,3-Apr-12 23:59:46.96101,12.36444

and I need to change the date format to this:

Tag1,2012-04-04 23:59:46.96101,12.36444

The word Tag1 is just a  example. It changes line by line.


Is there a good script to change this?
0
Comment
Question by:JCS_Unlimited
  • 9
  • 6
15 Comments
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Okay, this should do the job.  Save as a VBS file, let's say EE27663240.VBS.  Then run from a command line like this:

Either:

CSCRIPT EE27663240.VBS in.txt out.txt

or

CSCRIPT EE27663240.VBS in.txt

The first one will read the first file, and write the changed data to the second file name on the command line.  The second one will see there is no second file parm, and will overwrite the input file with the changes.
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  strOutFile = strInFile
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Loop through all lines of the file, insert when needed
For i = LBound(arrLines) To UBound(arrLines)
   If arrLines(i) <> "" Then
      arrFields = Split(arrLines(i), ",")
      arrDates = Split(arrFields(1), " ")
      Wscript.Echo arrDates(0)
      d = CDate(arrDates(0))
      arrDates(0) = Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0" & Day(d), 2)
      arrFields(1) = Join(arrDates, " ")
      arrLines(i) = Join(arrFields, ",")
   End If
Next

' Rewrite file with any changes made
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write(Join(arrLines, vbCrLf))
objFile.Close

Open in new window

~bp
0
 

Author Comment

by:JCS_Unlimited
Comment Utility
Bill,

When I run the above vbs It scrolls down showing the date multipel times and then shows this:


dconvert.vbs(30, 7) Microsoft VBScript runtime error: Sub
script out of range: '[number: 1]'
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Can you provide me with the data file you are testing against please.

~bp
0
 

Author Comment

by:JCS_Unlimited
Comment Utility
Maybe its from the error message when there is no data:
*[-103] No Data For This Point at This Time
dataoutput1.txt
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Yes, that will cause a problem, the program assumes the data is in the expected format.  We can add some code to skip those lines, are there any other lines that will need to be skipped?

~bp
0
 
LVL 51

Accepted Solution

by:
Bill Prew earned 500 total points
Comment Utility
This will avoid the error on that line, or any other line that starts with an asterick (*), but will leave it in the file, let me know if that is not what you wanted.
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  strOutFile = strInFile
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Loop through all lines of the file, insert when needed
For i = LBound(arrLines) To UBound(arrLines)
   If arrLines(i) <> "" And Left(arrLines(i), 1) <> "*" Then
      arrFields = Split(arrLines(i), ",")
      arrDates = Split(arrFields(1), " ")
      d = CDate(arrDates(0))
      arrDates(0) = Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0" & Day(d), 2)
      arrFields(1) = Join(arrDates, " ")
      arrLines(i) = Join(arrFields, ",")
   End If
Next

' Rewrite file with any changes made
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write(Join(arrLines, vbCrLf))
objFile.Close

Open in new window

~bp
0
 

Author Comment

by:JCS_Unlimited
Comment Utility
Bill,

That worked awesome.  It turns out that the output is in CST and I need to convert it to UTC. Is it possible to modify your code to do that? If not I can do that separately.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Yes, I'm pretty sure I have a sample of that from a prior question, let me dig it up...

~bp
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Give this a try.  If this isn't what you are after then I'll need more specifics.
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  strOutFile = strInFile
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Loop through all lines of the file, insert when needed
For i = LBound(arrLines) To UBound(arrLines)
   If arrLines(i) <> "" And Left(arrLines(i), 1) <> "*" Then
      arrFields = Split(arrLines(i), ",")
      arrDates = Split(arrFields(1), " ")
      d = UTC(CDate(arrDates(0)))
      arrDates(0) = Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0" & Day(d), 2)
      arrFields(1) = Join(arrDates, " ")
      arrLines(i) = Join(arrFields, ",")
   End If
Next

' Rewrite file with any changes made
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write(Join(arrLines, vbCrLf))
objFile.Close

Wscript.Echo UTC(Now())

Function UTC(d)
   Set oShell = CreateObject("WScript.Shell") 
   UTC = dateadd("n", oShell.RegRead("HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"), d) 
   Set oSkell = Nothing
End Function

Open in new window

~bp
0
 

Author Comment

by:JCS_Unlimited
Comment Utility
Hmm that didn't seem to do anything Bill.

I tried just adding 6 hours to the time loop you have, but that didn't seem to work correctly.
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Ahhh, silly me, The logic we had so far only manipulated the date, not the time.  So you want to adjust the time part (and possibly the date part if needed) to UTC, and then output it?

~bp
0
 

Author Comment

by:JCS_Unlimited
Comment Utility
Yes sir. I was able to manipulate the time, but if the time went over 24:00 I couldn't figure out how to change the date.

-Jas
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Okay, we should be able to work that.  For the times that have milliseconds, can that be dropped so that all the times are formatted as hh:mm:ss after the adjustments?

~bp
0
 

Author Comment

by:JCS_Unlimited
Comment Utility
Could we just Zero them out and attach them as 0's.?
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Give this version a try.
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm, if 2 parms entered
' use second as new output file, else rewrite to input file
If (WScript.Arguments.Count > 0) Then
  strInFile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
  strOutFile = WScript.Arguments(1)
Else
  strOutFile = strInFile
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Loop through all lines of the file, insert when needed
For i = LBound(arrLines) To UBound(arrLines)
   If arrLines(i) <> "" And Left(arrLines(i), 1) <> "*" Then
      arrFields = Split(arrLines(i), ",")
      arrDates = Split(arrFields(1), ".")
      d = UTC(CDate(arrDates(0)))
      arrDates(0) = Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0" & Day(d), 2) & " " & Right("0" & Hour(d), 2) & ":" & Right("0" & Minute(d), 2) & ":" & Right("0" & Second(d), 2)
      arrFields(1) = Join(arrDates, ".")
      arrLines(i) = Join(arrFields, ",")
   End If
Next

' Rewrite file with any changes made
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write(Join(arrLines, vbCrLf))
objFile.Close

Function UTC(d)
   Set oShell = CreateObject("WScript.Shell") 
   UTC = dateadd("n", oShell.RegRead("HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"), d) 
   Set oSkell = Nothing
End Function

Open in new window

~bp
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

772 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

10 Experts available now in Live!

Get 1:1 Help Now