Count Records in a text File (1 Viewer)

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi Folks,

I'm hoping someone can help me here, as I'm having difficulty in working out how to count records in a text file without having to import the text file into my database.

Here is the code I have so far:

Code:
DoCmd.Echo False, "Running Program" 'Indicates in the progress bar the program is running
    DoCmd.Hourglass True 'Turn on the Hourglass
    DoCmd.SetWarnings False 'Turn off warnings
    
    Dim FS As FileSystemObject 'Declare the File System Object
    Dim Folder As Folder 'Declare the folder object
    Dim subFolder As Folder 'Declare the sub folder object
    Dim File As File 'Declare the File object
    Dim rst As DAO.Recordset   'Declare rst as DAO Recordset
    Dim DB As Database   'Declare DB as the Database
    
    Set DB = CurrentDb   'Set DB as the current Database
    
    'Sets the FS variable to the CreateObject
    Set FS = CreateObject("Scripting.FileSystemObject")
    'AEG
    'If the aeg.txt file exists in the B:\aeg.fof folder then
    If FS.FileExists("B:\aeg.fof\aeg.txt") = True Then
        Set rst = DB.OpenRecordset("tblFileExists") 'Set rst to Open the tblFileExists table
        rst.AddNew 'Add a new record to the File Exists table
    
        'Add aeg.txt as a record to the tblFileExists table.
        rst!strFileExists = "aeg.txt"
        rst.Update ' write the record to the database
        rst.Close ' Close the recordset.
        Set rst = Nothing  'reset the rst variable to nothing
        DoEvents 'Passes control to the operating system so it can process other events
        'Update the progress bar
        DoCmd.Echo True, "Added AEG Text File: " & "aeg.txt"
    End If

I know that I need to add a field into my table whichwould be strRecordCount which would be place just as follows:

Code:
    'AEG
    'If the aeg.txt file exists in the B:\aeg.fof folder then
    If FS.FileExists("B:\aeg.fof\aeg.txt") = True Then
        Set rst = DB.OpenRecordset("tblFileExists") 'Set rst to Open the tblFileExists table
        rst.AddNew 'Add a new record to the File Exists table
    
        'Add aeg.txt as a record to the tblFileExists table.
        rst!strFileExists = "aeg.txt"
        [COLOR=red][B]rst!strRecordCount = [/B][/COLOR]
        rst.Update ' write the record to the database
        rst.Close ' Close the recordset.
        Set rst = Nothing  'reset the rst variable to nothing
        DoEvents 'Passes control to the operating system so it can process other events
        'Update the progress bar
        DoCmd.Echo True, "Added AEG Text File: " & "aeg.txt"
    End If

It's this part I'm having difficulty with! I'm stuck with what code do I need to write to just get the record count in the text file without having to import the text file. Every line in the text file is a single record, so I guess it's a matter of counting the number of lines in the text file that are not blank lines.

I know I could import the text file count the records in the table and then delete the contents of the table, but as I have a large number of text files I would prefer not to have to import them, but to have the ability to check to see if it exists, if it does count the number of records in it and enter that information into my table in my DB.

Any assistance would be most appreciated.

John
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:24
Joined
Jan 20, 2009
Messages
12,849
Personally I would not bother to read code with so much inane and utterly pointless commenting.
 

vbaInet

AWF VIP
Local time
Today, 10:24
Joined
Jan 22, 2010
Messages
26,374
Personally I would not bother to read code with so much inane and utterly pointless commenting.
Quite bizarre.

JohnLee, you need to open the file for reading and loop through each line with a counter variable. A search on here or on Google should yield useful results.
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Good day to you GalaxiomAtHome,

I'm sorry you feel that commenting is pointless, I've been told countless times by experience programmers that you can't comment enough, this helps those who may have to visit any code written to understand quickly and concisely what the code is doing.

I'm sorry you feel you have wasted your time, but thanks for just looking anyway, perhaps someone else might be more helpful to me.

Have a good day

John
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:24
Joined
Sep 12, 2006
Messages
15,613
either

a) read lines one at a time and count them (which is what vbainet said)

b) maybe you could just count crlfs

c) if they are fixed width, get the total byte size of the file, and divide by the line length.
 
Last edited:

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi Gemma,

Thanks for your response, I'll search for information on how to do that, and if I may come back to you if I have any difficulties that would be great

John
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi vbaInet,

Thanks for your pointer too, will look that up.

John
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi again Folks,

I've looked up a number of references and examples both in this forum and via google and I've got something that works to a point in that I get the total number of lines [records] in a text file, but can't get it to be assigned to the required field in my table! Here is my new code:

Code:
[COLOR=darkgreen]'AEG
    'If the aeg.txt file exists in the B:\aeg.fof folder then
[/COLOR]    [COLOR=blue]If [/COLOR][COLOR=blue]FS[/COLOR].FileExists("B:\aeg.fof\aeg.txt") = [COLOR=blue]True Then[/COLOR]
        
        [COLOR=blue]Dim[/COLOR] filenum [COLOR=blue]As Integer[/COLOR] [COLOR=darkgreen]'Declare the filenum variable
[/COLOR]        [COLOR=blue]Dim[/COLOR] count [COLOR=blue]As Long[/COLOR] [COLOR=darkgreen]'Declare the count variable as Long[/COLOR]
        [COLOR=blue]Dim[/COLOR] tmp [COLOR=blue]As String[/COLOR] [COLOR=darkgreen]'Declare the tmp variable as string
[/COLOR]        [COLOR=blue]Dim[/COLOR] RecordsCount [COLOR=blue]As Integer[/COLOR] [COLOR=darkgreen]'Declare the RecordsCount variable as[/COLOR] [COLOR=darkgreen]Integer
[/COLOR]        
        filenum = FreeFile [COLOR=darkgreen]'set filenum to FreeFile[/COLOR]
        [COLOR=darkgreen]'Open the aeg.txt text file for input as the file number[/COLOR]
        [COLOR=blue]Open[/COLOR] "B:\aeg.fof\aeg.txt" For Input As filenum
        [COLOR=darkgreen]'Loop while not the End of File
[/COLOR]        [COLOR=blue]Do While[/COLOR] [COLOR=blue]Not[/COLOR] EOF(filenum)
            [COLOR=blue]Line Input[/COLOR] #filenum, tmp$
            count = count + 1 [COLOR=darkgreen]'Count the lines and increment[/COLOR]
        [COLOR=blue]Loop[/COLOR]
        
        [COLOR=red]RecordsCount[/COLOR] = count [COLOR=darkgreen]'Assign the total count to the RecordsCount variable
[/COLOR]    
        [COLOR=blue]Set[/COLOR] rst = DB.OpenRecordset("tblFileExists") [COLOR=darkgreen]'Set rst to Open the tblFileExists table[/COLOR]
        rst.AddNew [COLOR=darkgreen]'Add a new record to the File Exists table[/COLOR]
    
        [COLOR=darkgreen]'Add aeg.txt as a record to the tblFileExists table.
[/COLOR]        rst!strFileExists = "aeg.txt"
        rst!lngRecordCount = [COLOR=red]RecordsCount
[/COLOR]        rst.Update [COLOR=darkgreen]'Write the record to the database[/COLOR]
        rst.Close [COLOR=darkgreen]'Close the recordset.
[/COLOR]        [COLOR=blue]Set[/COLOR] rst = [COLOR=blue]Nothing[/COLOR]  [COLOR=darkgreen]'Reset the rst variable to nothing
[/COLOR]        
        [COLOR=blue]Close[/COLOR] #filenum [COLOR=darkgreen]'Close the text file[/COLOR]
        
        DoEvents [COLOR=darkgreen]'Passes control to the operating system so it can process other events
[/COLOR]        [COLOR=darkgreen]'Update the progress bar
[/COLOR]        DoCmd.Echo [COLOR=blue]True[/COLOR], "Added AEG Text File: " & "aeg.txt"
    [COLOR=blue]End If[/COLOR]

So having assigned the result of count to the RecordsCount variable and then under the AddNew record section assigned the RecordsCount variable to the lngRecordCount field in the tblFileExists table, it should populate that field, but it doesn't.

I used the message box to check that a count was appearing the both the RecordsCount and count variables seperately and it was. I tried assigning the count variable directly to the lngRecordCount field under the AddNew record section and still no value was assigned to the field! in my table the lngRecordCount field is set to integer to match the type of the variable in my code and I even changed that to long integer to no avail.

Clearly I'm missing or doing something wrong, but can't work out what that might be, your assistance would be appreciated.

John
 

JANR

Registered User.
Local time
Today, 11:24
Joined
Jan 21, 2009
Messages
1,623
Code:
[COLOR=#0000ff]Set[/COLOR] rst = DB.OpenRecordset("tblFileExists")

Perhaps it will help to specify which type of recordset you want

Set rst = DB.OpenRecordset("tblFileExists", dbOpenDynaset)

JR
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi JANR,

Thanks for your quick response, I added that in, but it made no differance when I ran it with that in it.


John
 

vbaInet

AWF VIP
Local time
Today, 10:24
Joined
Jan 22, 2010
Messages
26,374
Does it throw up any error? Remove any error trapping so it takes you to the error line if it does.

Also, are you sure strFileExists and lngRecordCount are actual field names in tblFileExists?
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi vbaInet,

Thanks for your response, the fields do exist in the table, when I disabled the error trapping and ran the code, it was like it was in a continous loop. I had to use the task manager to exit out of my DB.

Any suggestions would be appreciated.

John
 

vbaInet

AWF VIP
Local time
Today, 10:24
Joined
Jan 22, 2010
Messages
26,374
Set a breakpoint in your code, run the code and step through it using F8. You should be able to spot the never ending loop.
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi vbaInet,

should the breakpoint be set within the loop part or outside of it. I also didn't mention that the strFileExists field gets populated, it's just the lngRecordCount field that doesn't.

John
 

vbaInet

AWF VIP
Local time
Today, 10:24
Joined
Jan 22, 2010
Messages
26,374
You can set the breakpoint at the start of the function. The problem may not be in the code that's being shown at the moment.

What's the typical value of RecordsCount? I would change that value from Integer to Long. Also ensure you've got that as the size of the lngRecordCount field as well.
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
Hi vbaInet,

Thanks for the pointers I will give all that a go, I think there could be something else that might be the cause of the problem, because I repeat the count records code again for each textfile and this may be where I'm going wrong, I'm going to break it all down and add each text file one at a time and see what happens. because I just commented out all the other text file codes and it worked for the first text file, so it looks like there may be another cause. I'll let you know once I start testing each one individually and then adding one to and existing one and see what happens.

Thanks once again for your help, no doubt I may call upon your assistance again.

John
 

vbaInet

AWF VIP
Local time
Today, 10:24
Joined
Jan 22, 2010
Messages
26,374
Yes John, that was what I was thinking. Maybe look at the criteria of your first loop and ensure that it breaks out of that loop.

Keep us in the loop of things :)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:24
Joined
Jan 20, 2009
Messages
12,849
I've been told countless times by experience programmers that you can't comment enough, this helps those who may have to visit any code written to understand quickly and concisely what the code is doing.

You have missed the point of commenting. The idea is to make code easy to understand. Putting a comment that adds no information whatsoever in every line distracts from the readability.

Dim count As Long 'Declare the count variable as Long

The comment simply repeats exactly what is already obvious in the command. A useful comment in declaring a variable might be to describe the nature of the value the variable is storing.

Having said that, intelligently chosen variable names are a better solution because they avoid the need to expain their purpose.

rst.Update 'Write the record to the database
rst.Close 'Close the recordset.
Set rst = Nothing 'Reset the rst variable to nothing

Once again the comments simply repeat the obvious meaning of the command. This group of lines without the comments is immediately recognisable to even a minimally experienced programmer at a glance. The comments distract from that recognition.

There is not one comment in the code that contributes in any way to the readabily and understanding of the procedure. All of it just gets in the way.

Comments about the flow of the program or the reason for less than obvious calculations are useful. A short description of the purpose of the whole procedure is often the most useful commenting.

One change that you should also implement in your programming technique is substituting constants for the file name and path, instead of using the string in the code.

Constants at the beginning can be easily changed. Changing the filename and path in your code means searching out and editing every line where they are used.
 

JohnLee

Registered User.
Local time
Today, 03:24
Joined
Mar 8, 2007
Messages
692
HI vabInet,

Well I've been through every bit of code one text file at a time and it's now placing a value in the lngRecordCount field in my table, the problem is that it accumulative for each text file, that is if my aeg text file has got 10 records in it, it puts that into the lngRecordCount field in my table, but then when it gets to the next text file that exists it adds the figure from the aeg text file to the record count of the next text file that exists. and it does this where any text files exist. My code now looks like this, but I can't seem to identify why its accumulating the record count for each text file to the next :

Code:
    [COLOR=blue]Dim[/COLOR] FS [COLOR=blue]As[/COLOR] FileSystemObject [COLOR=darkgreen]'Declare the File System Object[/COLOR]
    
    [COLOR=blue]Dim[/COLOR] rst [COLOR=blue]As[/COLOR] DAO.Recordset [COLOR=darkgreen]'Declare rst as DAO Recordset[/COLOR]
    [COLOR=blue]Dim[/COLOR] DB [COLOR=blue]As[/COLOR] Database [COLOR=darkgreen]'Declare DB as the Database[/COLOR]
    
    [COLOR=blue]Dim[/COLOR] filenum [COLOR=blue]As Integer[/COLOR] [COLOR=darkgreen]'Declare the filenum variable[/COLOR]
    [COLOR=blue]Dim[/COLOR] count [COLOR=blue]As Long[/COLOR] [COLOR=darkgreen]'Declare the count variable as Long[/COLOR]
    [COLOR=blue]Dim[/COLOR] tmp [COLOR=blue]As String[/COLOR] [COLOR=darkgreen]'Declare the tmp variable as string[/COLOR]
    [COLOR=blue]Dim[/COLOR] RecordsCount [COLOR=blue]As Long[/COLOR] [COLOR=darkgreen]'Declare the RecordsCount variable as Integer
[/COLOR]    
    [COLOR=blue]Const[/COLOR] ForReading = 1 [COLOR=darkgreen]'Declare the FoReading Constant[/COLOR]
    
    [COLOR=blue]Set[/COLOR] DB = CurrentDb [COLOR=darkgreen]'Set DB as the current Database[/COLOR]
    
    [COLOR=darkgreen]'Sets the FS variable to the CreateObject
[/COLOR]    [COLOR=blue]Set[/COLOR] FS = CreateObject("Scripting.FileSystemObject")
    [COLOR=darkgreen]'AEG
    'If the aeg.txt file exists in the B:\aeg.fof folder then
[/COLOR]    [COLOR=blue]If[/COLOR] FS.FileExists("B:\aeg.fof\aeg.txt") = [COLOR=blue]True Then[/COLOR]
        
        filenum = FreeFile [COLOR=darkgreen]'Set filenum to FreeFile[/COLOR]
        [COLOR=darkgreen]'Open the aeg.txt text file for input as the file number[/COLOR]
        [COLOR=blue]Open[/COLOR] "B:\aeg.fof\aeg.txt" [COLOR=blue]For Input As[/COLOR] filenum
        [COLOR=darkgreen]'Loop while not the End of File
[/COLOR]        [COLOR=blue]Do While Not[/COLOR] EOF(filenum)
            [COLOR=blue]Line Input[/COLOR] #filenum, tmp$
            count = count + 1 [COLOR=darkgreen]'Count the lines and increment[/COLOR]
        [COLOR=blue]Loop[/COLOR]
        
        RecordsCount = count [COLOR=darkgreen]'Assign the total count to the RecordsCount variable
[/COLOR]        
        [COLOR=blue]Close[/COLOR] #filenum [COLOR=darkgreen]'Close the text file[/COLOR]
        
        Set rst = DB.OpenRecordset("tblFileExists") [COLOR=darkgreen]'Set rst to Open the tblFileExists table[/COLOR]
        rst.AddNew [COLOR=darkgreen]'Add a new record to the File Exists table[/COLOR]
    
        [COLOR=darkgreen]'Add aeg.txt as a record to the tblFileExists table.
[/COLOR]        rst!strFileExists = "aeg.txt"
        rst!lngRecordCount = RecordsCount [COLOR=darkgreen]'Assign the value of the RecordsCount to the lngRecordCount Field
[/COLOR]        rst.Update [COLOR=darkgreen]'Write the record to the database[/COLOR]
        rst.Close [COLOR=darkgreen]'Close the recordset[/COLOR].
        [COLOR=blue]Set[/COLOR] rst = [COLOR=blue]Nothing[/COLOR]  [COLOR=darkgreen]'Reset the rst variable to nothing[/COLOR]
        
        RecordsCount = 0 [COLOR=darkgreen]'Set the RecordsCount variable to zero[/COLOR]
        
        DoEvents [COLOR=darkgreen]'Passes control to the operating system so it can process other events
[/COLOR]        [COLOR=darkgreen]'Update the progress bar[/COLOR]
        DoCmd.Echo [COLOR=blue]True[/COLOR], "Added AEG Text File: " & "aeg.txt"
    [COLOR=blue]End If[/COLOR]

The above code is repeated for each text file that may exist and if it does then the code for that text file runs.

I put message boxes before the "RecordCount = 0" and a value appeared before but was set to zero after, so the RecordCount variable is being reset to zero! So I'm not sure where I look to find why I'm getting accumulative values as it works through each text file that exists.

Any help you can offer would be most appreciated.

John
 

vbaInet

AWF VIP
Local time
Today, 10:24
Joined
Jan 22, 2010
Messages
26,374
Where you've put RecordsCount = 0 also put count = 0
 

Users who are viewing this thread

Top Bottom