Solved

A date format changing script.

Posted on 2012-04-04
15
101 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 53

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 53

Expert Comment

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

~bp
0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

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 53

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 53

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 53

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 53

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 53

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 53

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 53

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

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…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

770 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