need to return total record count, but here is the trick...


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: need to return total record count, but here is the trick...

  1. #1
    Join Date
    Jul 2005
    Posts
    1

    need to return total record count, but here is the trick...

    Here is the problem, I do not code enough and trying to get a solution that end user points at a zip file and returns total count of all records in each file for a one number returned value. Such as [ month of MAY count = 11,501 ]

    The nuances are kicking my butt, I have tried to use Excel and VB Script it to step through and I am getting stuck, I tried in ACCESS to get active connect syntax to load all raw data then return total count, and getting stuck..

    There must be a simpler way, all I need is row count total.... and world peace, but I will settle for just total record count...

    given the below pseudo code steps... what would be the simplest solution?

    more details...


    each month a ZIP is sent
    each ZIP contains files (+/- 25 files) that are exe, but with no extension
    each exe unpacks to active root, 12 DAT files
    can only unpack one exe at a time since each 12 DAT files has same name
    the DAT files are text, fixed length and length not consistent amongst the 12, but each AR.DAT from each of the exe files has field length consistent amongst the total group of like AR.DAT files)

    the important goal is to get total count of all records in ZIP file.

    display as one total count


    1. unzip file
    (20-30 files in each zip)
    2. do while not EOF
    change so each file name ends in .EXE

    3. create a folder
    4. do while not EOF
    a. step through files and
    a.1. copy a file into folder
    a.2. run the exe file
    a.3. do while not EOF
    b.1. if size is 0 bytes, goto next DAT
    b.2. read each of the 12 DAT files
    b.3. count number of records
    b.4. keep running sum of number of records
    a.4. clear the 12 DAT files
    5. display running sum results of total records in each dat in all files

    ========================

    ideas???

  2. #2
    Join Date
    Nov 2003
    Location
    Alameda, CA
    Posts
    1,737
    I agree with peace on earth, and I wish I had a solution for that.

    You stated your problem very clearly. As all other problems, there is a (finite) number of solutions, that depend on which tools you have/can/know-how to use. What is not clear from your post is in which part(s) you have problems. This is not a complicated task, but involves unpacking files, reading folders, read files and line counting. Not easy to do with Excel, or VBA (although possible), I do not know about Access. Using VB6 (like any other more all-porpouse languages) is the best solution.
    Please let us know which are your knowledge/requirements

    Marco
    "There are two ways to write error-free programs. Only the third one works."
    Unknown

  3. #3
    Join Date
    May 2004
    Posts
    21
    Use the code below to count the lines in your flat files.
    You must have a reference to Microsoft activex objects library in your project.

    In addition, because the JET provide will not recognize the file extension RDY, you will have to modify your registry such that it can recognize it.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions
    Most likely DisabledExtensions will be set to this:
    !txt,csv,tab,asc,tmp,htm,html
    Double-click DisabledExtensions, and add a comma and the word log to the end of the value, like so:
    !txt,csv,tab,asc,tmp,htm,html,rdy



    Sub main()

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1

    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    objConnection.cursorlocation = adUseClient

    strPathtoTextFile = "C:\temp\"

    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=60;" & _
    "Data Source=" & strPathtoTextFile & ";" & _
    "Extended Properties=""text;HDR=NO;FMT=FixedLength"""

    objRecordset.Open "SELECT * FROM TRAN1001.RDY", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText


    Debug.Print objRecordset.recordcount



    End Sub
    Siva

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center