Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to split data file

Posted on 2011-03-01
7
Medium Priority
?
319 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 58

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 58

Accepted Solution

by:
Bill Prew earned 2000 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
Industry Leaders: 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 58

Expert Comment

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

~bp
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

618 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