Solved

A date format changing script.

Posted on 2012-04-04
15
105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 56

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 56

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 56

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 56

Accepted Solution

by:
Bill Prew earned 500 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 56

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 56

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 56

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 56

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 56

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] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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