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:
I know that I need to add a field into my table whichwould be strRecordCount which would be place just as follows:
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
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