Solved

A date format changing script.

Posted on 2012-04-04
15
104 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 54

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 54

Expert Comment

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

~bp
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 54

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 54

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 54

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 54

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 54

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 54

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 54

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

751 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