Solved

How to split data file

Posted on 2011-03-01
7
311 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
[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
  • 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 54

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 54

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
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: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 54

Expert Comment

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

~bp
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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