Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

A date format changing script.

Posted on 2012-04-04
15
Medium Priority
?
107 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 59

Expert Comment

by:Bill Prew
ID: 37809228
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
ID: 37814496
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 59

Expert Comment

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

~bp
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

Expert Comment

by:Bill Prew
ID: 37817576
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 59

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 37817950
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
ID: 37825674
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
 
LVL 59

Expert Comment

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

~bp
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 37826067
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
ID: 37829679
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 59

Expert Comment

by:Bill Prew
ID: 37830592
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
ID: 37830610
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 59

Expert Comment

by:Bill Prew
ID: 37830627
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
ID: 37830655
Could we just Zero them out and attach them as 0's.?
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 37830668
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

773 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