Importing last five lines from all text files in a direcory (1 Viewer)

Erneye

New member
Local time
Today, 01:56
Joined
Jun 24, 2012
Messages
2
Hi,
I am very new to VBA started trying to use it Friday :)
I am trying to create something that will read the last five lines from all the comma delimited text files in a directory - the amount of files can vary
The files contain lists of all user that have logged into a pc for past year
date, pc name, user id,ip address
- some files only have for or 5 entries, some have hundreds
I only need the last five ideally in reverse order - latest entry first
I have a bit of code i'm using that gives me all the entries in all the files which I found in the net
I then have some code that reads a single comma sepaerated text file with no file extension into another table
This is an archived document containing user details for each user id
I then perform a join on the id to give me full details
It works ok but I would really like reduce the number of results to 5 entries per PC
I cant include the files because they are confidential
The code I have for reading the files in is

Sub getallfiles()

Dim f As Object '//File Object
With CreateObject("Scripting.FileSystemObject")
For Each f In .GetFolder("C:\Users\Ernie\Desktop\work\we2").Files

DoCmd.TransferText , , "MyTable", f.Path, False 'import file
DoEvents 'don't take over all of the PC resources
Next f
End With
CurrentDb.TableDefs("MyTable").Fields("F1").Name = "Date"
CurrentDb.TableDefs("MyTable").Fields("F2").Name = "PC1"
CurrentDb.TableDefs("MyTable").Fields("F3").Name = "User"
CurrentDb.TableDefs("MyTable").Fields("F4").Name = "IP"
End Sub

The path is my local pc but for real use will be a folder on a network drive

I've seen code to read the last five lines by reading the lines into an array and iterating through them backwards but dont know how to incorperate them into the code that works so far
Is there a way of making this code do what I want or a better way of achiving the whole thing
lots of comments would be a help too I would like to understand the code to help me learn
Thanks in advance
Ernie
 

WayneRyan

AWF VIP
Local time
Today, 08:56
Joined
Nov 19, 2002
Messages
7,122
Erneye,

You can put the following code on the OnClick event of a command button.
If you use the Debugger and single-step through and observe the action, it should
be pretty easy to get your data into your table.

Code:
Dim varArray As Variant
Dim intRecord As Long
Dim strBuffer As String
Dim strFileName As String
Dim strRecords(5) As String
Dim rstData As DAO.Recordset
Dim i As Integer
'
' Put the Results in YourTable
'
Set rstData = CurrentDb.OpenRecordset("Select * From YourTable")
'
strFileName = Dir("C:\Users\Ernie\Desktop\work\we2\*.*")
While strFileName <> ""
   '
   ' Open each file, saving the last 5 records (using the Mod function)
   '
   Close #1
   Open strFileName For Input As #1
   Line Input #1, strBuffer
   intRecord = 1
   strRecords(0) = strBuffer
   While Not EOF(1)
      Line Input #1, strBuffer
      intRecord = intRecord + 1
      strRecords(intRecord Mod 5) = strBuffer
      Wend
   '
   ' File has been read, put the last five records into the table
   '
   For i = intRecord Mod 5 To 0 Step -1
     varArray = Split(strRecords(i), ",")
     rstData.Addnew
     rstData.Fields(0) = varArray(0)
     rstData.Fields(1) = varArray(1)
     rstData.Fields(2) = varArray(2)
     rstData.Fields(3) = varArray(3)
     rstData.Update
     Next i
   '
   For i = 4 to intRecord mod 5 Step -1
      varArray = Split(strRecords(i), ",")
      rstData.Addnew
      rstData.Fields(0) = varArray(0)
      rstData.Fields(1) = varArray(1)
      rstData.Fields(2) = varArray(2)
      rstData.Fields(3) = varArray(3)
      rstData.UpDate
      Next i
   '
   strFileName = Dir()
   Wend

hth,
Wayne
 

Erneye

New member
Local time
Today, 01:56
Joined
Jun 24, 2012
Messages
2
Hi,
Thanks Wayne
I'm getting a file not found error the line below -

open strFilename For Input as #1

The files exist in the folder set in strFilename
I can open them ok in the orignal code I had
I'm going to try on my home pc - thats where I tested the original code and I forgot to bring it to work this morning - Monday mornings are never the best :)
Will get back to you when I've tried that
Ernie
 

mdlueck

Sr. Application Developer
Local time
Today, 04:56
Joined
Jun 23, 2011
Messages
2,631
I only need the last five ideally in reverse order - latest entry first

For this requirement I would suggest the tail command from the GnuWin32 project, found at: http://gnuwin32.sourceforge.net/

Tail is the command to return the last N lines of a file.That is native compiled C/C++ code rather than Interpretive VBA code.
 

Users who are viewing this thread

Top Bottom