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?
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
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
"There are two ways to write error-free programs. Only the third one works."
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.
Most likely DisabledExtensions will be set to this:
Double-click DisabledExtensions, and add a comma and the word log to the end of the value, like so:
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 & ";" & _
objRecordset.Open "SELECT * FROM TRAN1001.RDY", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL