Solved

How to split data file

Posted on 2011-03-01
7
304 Views
Last Modified: 2012-05-11
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
Comment
Question by:BigBobBK
  • 3
  • 2
  • 2
7 Comments
 
LVL 14

Expert Comment

by:Psy053
ID: 35013178
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
 
LVL 51

Expert Comment

by:Bill Prew
ID: 35013514
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
 
LVL 51

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 35013671
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:BigBobBK
ID: 35014033
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
 
LVL 14

Expert Comment

by:Psy053
ID: 35014612
Thanks for the clarification, looks like Bill has it covered though.
0
 

Author Closing Comment

by:BigBobBK
ID: 35023864
works perfectly
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 35023935
Great, glad that got it, thanks.

~bp
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now