• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

How to split data file

I have a data file with (E_R extension) that contains multiple days of data.  I need to break this one large data file into separate files by date and named YYMMDD.E_R.  Each file needs to contain data from 3:45am till 3:44am for the day. I've included this file but had to change the extension from .E_R to .txt.

Can someone help me get this accomplished?  I'm not too knowledgable in coding so any help would be appreciated.

RLG110301.txt
0
BigBobBK
Asked:
BigBobBK
  • 3
  • 2
  • 2
1 Solution
 
Psy053Commented:
Sorry, just to clarify:

Given that you want data from 0345 to 0344, that would mean, for example, that this line:
    18747, 02/24/11,          ,        , 03:31:42AM, A,   , DriveOff  LINE    

Would be in the file:
110223.E_R

0
 
Bill PrewCommented:
Here's what I have so far, just have to add the time cut off logic.  Save as a VBS and then run as follows:

cscript EE26856829.vbs RLG110301.txt

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
  sInfile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If

' Create file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Open input file for reading
Set oInfile = oFSO.OpenTextFile(sInfile, ForReading, False, TriStateUseDefault)

' Get path for output files
Set oTemp = oFSO.Getfile(sInfile)
sPath = oTemp.ParentFolder
Set oTemp = Nothing

' Read input file, split chunks to seperate files
dLastDate = DateValue("1/1/1900")
Set oOutFile = Nothing
Do While Not oInFile.AtEndOfStream
  sRead = oInFile.ReadLine
  aToken = Split(sRead, ",")
  dThisDate = DateValue(Trim(aToken(1)))
  dThisTime = TimeValue(Replace(Replace(Trim(aToken(4)), "AM", " AM"), "PM", " PM"))
  If dThisDate <> dLastDate Then
    dLastDate = dThisDate
    If Not oOutFile Is Nothing Then oOutFile.Close
    sStamp = Right(Year(dThisDate), 2) & Right("0" & Month(dThisDate), 2) & Right("0" & Day(dThisDate), 2)
    Set oOutFile = oFSO.OpenTextFile(sPath & "\" & sStamp & ".E_R", ForWriting, True)
  End If
  oOutFile.WriteLine(sRead)
Loop

' Cleanup and end
oInfile.Close
Set oInfile = Nothing
If Not oOutFile Is Nothing Then 
   oOutFile.Close
   Set oOutFile = Nothing
End If
Set oFSO = Nothing
Wscript.Quit

Open in new window

~bp
0
 
Bill PrewCommented:
Okay, this adjustment should handle the time issue.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
  sInfile = WScript.Arguments(0)
Else
  WScript.Echo "No filename specified."
  WScript.Quit
End If

' Create file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Open input file for reading
Set oInfile = oFSO.OpenTextFile(sInfile, ForReading, False, TriStateUseDefault)

' Get path for output files
Set oTemp = oFSO.Getfile(sInfile)
sPath = oTemp.ParentFolder
Set oTemp = Nothing

' Read input file, split chunks to seperate files
dLastDate = DateValue("01/01/2000")
dLastTime = TimeValue("03:45:00")
sStamp = Right(Year(dLastDate), 2) & Right("0" & Month(dLastDate), 2) & Right("0" & Day(dLastDate), 2)
Set oOutFile = oFSO.OpenTextFile(sPath & "\" & sStamp & ".E_R", ForWriting, True)
Do While Not oInFile.AtEndOfStream
   sRead = oInFile.ReadLine
   aToken = Split(sRead, ",")
   dThisDate = DateValue(Trim(aToken(1)))
   dThisTime = TimeValue(Replace(Replace(Trim(aToken(4)), "AM", " AM"), "PM", " PM"))
   If dThisDate <> dLastDate Then
      If dThisTime > dLastTime Then
         dLastDate = dThisDate
         oOutFile.Close
         sStamp = Right(Year(dThisDate), 2) & Right("0" & Month(dThisDate), 2) & Right("0" & Day(dThisDate), 2)
         Set oOutFile = oFSO.OpenTextFile(sPath & "\" & sStamp & ".E_R", ForWriting, True)
      End If
   End If
   oOutFile.WriteLine(sRead)
Loop

' Cleanup and end
oInfile.Close
Set oInfile = Nothing
If Not oOutFile Is Nothing Then 
   oOutFile.Close
   Set oOutFile = Nothing
End If
Set oFSO = Nothing
Wscript.Quit

Open in new window

~bp
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
BigBobBKAuthor Commented:
Psy053:  You are correct.  To clarify:  the line " 18747, 02/24/11,          ,        , 03:31:42AM, A,   , DriveOff  LINE "  would be in the file 110223.E_R.

0
 
Psy053Commented:
Thanks for the clarification, looks like Bill has it covered though.
0
 
BigBobBKAuthor Commented:
works perfectly
0
 
Bill PrewCommented:
Great, glad that got it, thanks.

~bp
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now