Solved

How to split data file

Posted on 2011-03-01
7
305 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 52

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 52

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 52

Expert Comment

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

~bp
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

863 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

19 Experts available now in Live!

Get 1:1 Help Now