Solved: Read 'live' LOG file

[Felix Atagong]

I'm trying to read the new added lines from a 'live' log file (flat text) every minute and add them on an Excel sheet. For the moment I only have found some macros that read the complete log but I only need the last line.

Other question: will it be possible to read the file when it is always being written into by the original application?

Sub ReadTextFile()
Dim FileNum As Integer
Dim r As Integer
Dim wb As Workbook
Dim Data As String
r = 1
FileNum = FreeFile
Set wb = Workbooks.Add
Open "Europe.txt" For Input As #FileNum
' Pagename is the name of the text file I want to open
Do While Not EOF(FileNum)
Line Input #FileNum, Data
ActiveSheet.Cells(r, 1) = Data
r = r + 1
Loop
Close #FileNum
End sub

[lucas]

could you post the text file or an example and tell us exactly what you are trying to retrieve from it? Does the line always start with a specific word?

[malik641]

If you just need the last line, just let the loop go through the file (overwriting Data each time), and THEN put the last value in Data in the cell:

Sub ReadTextFile()
Dim FileNum As Integer
Dim r As Integer
Dim wb As Workbook
Dim Data As String
r = 1
FileNum = FreeFile
Set wb = Workbooks.Add
Open "Europe.txt" For Input As #FileNum
' Pagename is the name of the text file I want to open
Do While Not EOF(FileNum)
Line Input #FileNum, Data

' r = r + 1
Loop
ActiveSheet.Cells(r, 1) = Data
Close #FileNum
End Sub
Not the most elegant way, but an easy quick-fix.

[lucas]

Hey sql guy....that works great. I commented your code to show how to place the data exactly where you want it and how to put it in the activeworkbook....

Sub ReadTextFile()
Dim FileNum As Integer
Dim r As Integer
Dim wb As Workbook
Dim Data As String
'row number in sheet to place the data
r = 10
FileNum = FreeFile
' Set wb = Workbooks.Add
Set wb = ActiveWorkbook
Open "test.txt" For Input As #FileNum
' Pagename is the name of the text file I want to open
Do While Not EOF(FileNum)
Line Input #FileNum, Data

' r = r + 1
Loop
' change the 2 to the column number where you want to place the data
ActiveSheet.Cells(r, 2) = Data
Close #FileNum
End Sub

[malik641]

Hey sql guy....
I'm waiting for that forum to start gettin' some action! :)


I commented your code to show how to place the data exactly where you want it and how to put it in the activeworkbook....
Show off :rofl:

[Felix Atagong]

Well, these log files can grow quite big, if nobody deletes it from time to time it will get to a few megs.
But I guess I could read the complete file the very first time and put a counter to what line number I could read (example line 546).
Then after a minute I could reopen and start reading from line 546 to EOF.

About sending an example I would first have to 'delete' all the stuff you people may not see (it's from the company I work at).

[Felix Atagong]

:dunno

[Felix Atagong]

Sorry can't find a delete button... :banghead: posted this 3 times by mistake

[lucas]

won't the last line always be the last line?

[lucas]

is the new data added to the beginning or the end of the text file?

[Felix Atagong]

No. In a minute another 10 to 30 lines may have been added. So i will need to read all added lines since the last time. Of course not taking care of lines that say things like: user 1234 logged in/off... or print job 6549 started or something...

[lucas]

so if in a minute 10-30 lines are added....are you still looking for the last line even after the added lines? If so then Josephs code will still work.

[lucas]

So i will need to read all added lines since the last time
I just read this....I'm lost as to what you're trying to do....

[malik641]

What if you could somehow 'link' excel to the text file through MS Query or something and just refresh the pivot table to add all new records?

...I'll try this real quick.

[Felix Atagong]

Yeah i'm quite confused myself.
I have this logfile and new items are written to the end of it.
Somewhere in between all the gobbledygook is some data I need. I need only that data (I know how to filter in VBA no problem).

Example (fictitious):
070829141310 1234 succesfully logged in
070829141325 5678 logged out
070829141336 R98765432CRFM1202Y
070829141402 6289 started
070829141409 R89764533MANQ1345N

The first time I open the log file I need to read all lines until EOF and take out the data I need. I close the log file.

After a minute I will reopen the file again. 10 new lines (can be less, can be more) will have been added to the end. I only want to read those last added lines (because I already have all the previous data on my excel sheet, remember) and take out the 2 or 3 lines that contain the data I'm interested in.

[Felix Atagong]

What if you could somehow 'link' excel to the text file through MS Query or something and just refresh the pivot table to add all new records?
...I'll try this real quick.

Now that's an interesting idea. My ideas are not quite clear yet, so I'll think all this business over... i'll come back if I have something more 'specific' to show you...

[malik641]

Yeah, that should work for you. Forget the VBA on this one.

This is a bit confusing..but I'll try my best to explain.

-Click on Data-->Pivot Table and Pivot Chart Report
-Choose External Data Source
-Make sure PivotTable is the report format
-Click Get Data
-Now click <New Data Source> Press OK
-You can give whatever name you want for the data source. I chose "Text Files_TEST"
-For number 2, I chose Microsoft Text Driver (near the bottom of the list)
-Click Connect
-In the ODBC Text Setup dialog, uncheck Use Current Directory, then select "Select Directory..." and locate the directory where the text file is.
-Press OK. It will then give you a list of .txt files OR .csv files to choose to connect to. Choose the file you need to see. Press OK.
-Place your pivot table however you need it
-You can have the pivot table refresh as often (in minutes) as you need it

The only thing I don't think you'll like with this, is if there is duplicate data, you will only see 1 row of that data (I haven't found a way around that with pivot tables).

Another alternative is to import the text file directly, and it can refresh however often (in minutes) as you want also...the problem is that when it does refresh the data, Excel asks you over and over again which file to choose from....I don't know how to say "This is the file...always and forever". :dunno

All in all, you don't need VBA to accomplish this task :)

[Tommy]

Why not read the file with LOF() and split on vbCrLf for the array of lines and look from there?
EX:

Public Function GetStrFile$(iFName$)
mFNo = FreeFile
Open iFName For Input As #mFNo
mLen& = LOF(mFNo)
GetStrFile = Input(mLen, #mFNo)
Close #mFNo
End Function
Function a()
Arr = Split(GetStrFile("SomeFileSomePlaceButThisIsReservedForTheFullName"), vbCrLf)
End Function


Arr will be an array of strings with ubound = number of lines in the file.

Just a thought. :)

[malik641]

Aha!!! Even better (than my last idea...not against yours, Tommy :)).

Ok, this is exactly what I believe you need:

-Click on Data-->Import External Data-->Import Data...
-Now click <New Data Source> Press OK
-You can give whatever name you want for the data source. I chose "Text Files_TEST"
-For number 2, I chose Microsoft Text Driver (near the bottom of the list)
-Click Connect
-In the ODBC Text Setup dialog, uncheck Use Current Directory, then select "Select Directory..." and locate the directory where the text file is.
-Press OK. It will then give you a list of .txt files OR .csv files to choose to connect to. Choose the file you need to see. Press OK.
-Select all the fields to import from the text file (a dialogue box in MS Query will have the table listed with 'headers' even if they're not actually headers)
-Then import
-To have this refresh...after it's been imported, click Data-->Import External Data-->Data Range Properties...-->Check the 'Refresh Every' and fill in the increment

This will show all the data, repeats or not.

:thumb

[malik641]

And as a side note, in the Data-->Import External Data-->Data Range Properties-->Uncheck the 'include field names' to get rid of the headers if there are none (this will need a refresh to reflect changes)

[Charlize]

I thought about something like this. In the beginning there is no lineno stored. After first time we store a lineno. Next time we loop until lineno is > than and up we go, append new lines to the sheet. Not tested.Sub ReadTextFile()
Dim FileNum As Integer
Dim r As Integer
Dim Data As String
Dim lastfetchedline As Long
Dim lrow As Long
'c1 holds the last fetched line
'first time it is zero
lastfetchedline = ActiveSheet.Cells(1, 3)
lrow = Range("A" & Rows.Count).End(xlUp).Row
'is lineno of the file
r = 0
FileNum = FreeFile
Open "Europe.txt" For Input As #FileNum
' Pagename is the name of the text file I want to open
Do While Not EOF(FileNum)
r = r + 1
'if lineno < 0 just read data and go to next line
If r < lastfetchedline Then
Line Input #FileNum, Data
Else
Line Input #FileNum, Data
lrow = lrow + 1
ActiveSheet.Cells(lrow, 1) = Data
End If
Loop
'Store last read line of file in C1
ActiveSheet.Cells(1, 3).Value = r
Close #FileNum
End Sub

[Felix Atagong]

Thanks guy for this info. I found a quick 'n dirty solution that works as well.

My log file is generated on a network drive that I have given a drive letter F: on my PC (//server/status/mt000001.fsd)

My macro moves the mt000001.fsd file from the F drive to my C drive. The log file has now been deleted from the server. I read the data (up till EOF) into my Excel sheet using a macro. If new log lines are generated the apllication on the F server will generate a new file with exactly the same name.

I read the new file again and appends on my Excel sheet.

It works!


Kill "C:/STATUS/MT000001.FSD"
OldFilePath = "F:/STATUS/MT000001.FSD"
NewFilePath = "C:/STATUS/MT000001.FSD"

[Charlize]

Thanks guy for this info. I found a quick 'n dirty solution that works as well.

My log file is generated on a network drive that I have given a drive letter F: on my PC (//server/status/mt000001.fsd)

My macro moves the mt000001.fsd file from the F drive to my C drive. The log file has now been deleted from the server. I read the data (up till EOF) into my Excel sheet using a macro. If new log lines are generated the apllication on the F server will generate a new file with exactly the same name.

I read the new file again and appends on my Excel sheet.

It works!


Kill "C:/STATUS/MT000001.FSD"
OldFilePath = "F:/STATUS/MT000001.FSD"
NewFilePath = "C:/STATUS/MT000001.FSD"

How do you delete the file on the shared map of the server (your f: drive) ? Do you have administrator rights on that mapped drive. If not, you can't delete that file. Just curious, because I can't delete a file of a mapped network drive if I don't have the rights to do so (And I will most likely not change this in the near feature). I can take a backup copy to a usb stick but not a move. User maps can be used for storing, deleting, ... documents etc. but not system kinda maps. Only the administratoraccount of the server can do such a thing.

[Felix Atagong]

It is not a real server, just an ordinary xp pc dedicated to run our ftp for file transmission. I move the log file from server to personal pc without a problem. By moving it gets deleted as well i.e. the file isn't there anymore.