Readfile in reverse (Read the 5th last line)

[huskies]

Is there a way to read a file in reverse. The only way that I know right now is to read the entire file, store it in an array and spit it out in reverse. This is not a good way because if I have a large text file this will really slow down my script.

My situation, I'm trying to do a series of SQL queries using putty, after each query the output looks like this. I know the number I want is always at the 5th last line of the file, where the date is after the first pipe and the number I want is after the second pipe. again I don't want to read the entire file because if I run this code 50 times, then the putty log (a text file) will be enormous and reading the whole thing everytime is not a good idea.

mysql> select date, sum(visitors) folks
    -> FROM clpr1_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
    -> GROUP BY date_fk;
+------------+-------+
| date     | folks |
+------------+-------+
| 2011-10-27 |  1462 |
| 2011-10-28 |   858 |
| 2011-10-29 |   725 |
| 2011-10-30 |  1289 |
| 2011-10-31 |  1214 |
| 2011-11-01 |  1585 |
| 2011-11-02 |  2555 |
| 2011-11-03 |  1359 |
| 2011-11-04 |  1798 |
| 2011-11-05 |  1858 |
| 2011-11-06 |  2837 |
| 2011-11-07 |  1769 |
| 2011-11-08 |   822 |
+------------+-------+
1162 rows in set (1.72 sec)

mysql>

My code for reading the last line of the text file is as follows and I find that it works fine 99% of the time

Func ReadLog($PuttyLogpath, $Message)
    ;puttylog reading
    ;$message: the message to wait for before inputting the next command, e.g mysql> indicates that the sql is ready for the next input
    sleep(200)
    $file = fileopen($PuttyLogpath,0)  ;open the text file
    Local $str = FileReadLine($file, -1) ; and read the very last line
    fileclose($file)
    while  $str <> $Message   ;the putty log will be update many times a second by putty itself and wherever there are updates in the command line, the putty log will reflect that
        sleep(200)
        $file = fileopen($PuttyLogpath,0)
        $str = FileReadLine($file, -1)
        fileclose($file)
        ConsoleWrite($str)
    WEnd
  

    if fileclose($file)=1 Then
        fileclose($file)
    endif
EndFunc

[kylomas]

huskies,

we just had another of these "works 99% of the time" and it also had to do with resource serialization. Is it possible that the failure is related to the file being updated as you are reading it?

kylomas

[huskies]

kylomas

Thanks for your reply. My question was not "why is my code not working for that 1% of the time" I wanted to know if there was a way to read the 5th last line of a file without reading the entire file first, which my script couldn't do currently

[kylomas]

huskies,

I would read the file to an array and process the ubound($array)-5 element. Another possibility is filesetpos($file_end) and somehow backing up 5 records. I have no experience with this, however.

kylomas

[sleepydvdr]

Would something like this work for you?

#include <file.au3>

$file = "filename.txt"
$fileLength = _FileCountLines
$read = FileReadLine($file, $fileLength - 5)

[kylomas]

@sleepydrvr,

_filecountlines creates an array and returns element count.

@huskies,

CRAZY Solution - get one of the regex expert to find the 6TH last CRLF or LF (depending on your file) and strip out the string to the next CRLF or LF, depending. (just got a headache thinking this up!!)

kylomas

[Zedna]

Try to use this SELECT with descencing order so last date will be listed as first and you can read it easily from begining.

select date, sum(visitors) folks
FROM clpr1_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
GROUP BY date_fk
ORDER BY date DESC

Edited November 9, 2011 by Zedna

[BrewManNH]

Couldn't you just format your SQL query to return only the information you're looking for, rather than a lot of data that's unnecessary? What you're doing is taking the data, writing it to a text file, and then retrieving it from that text file. If what is in the text file was formatted correctly in the first place (I'm assuming you're writing your own queries here), wouldn't it eliminate the problem?

[BrewManNH]

BTW, _FileCountLines doesn't eliminate the problem with loading the whole file into memory and then dealing with it, because one of the first functions that it uses is FileRead and then does a string split to get the number of lines, not very efficient for long files.

[kylomas]

huskies,

Reading the file line by line is not a good idea, however, fileread may be acceptible. Just how big is big?

@zedna and BrewmanNH

What are you running this select stmt against?

kylomas

[huskies]

@kylomas

I'm going to give a filesetpos try, it looks somewhat promising REGEX might work, it's on my list of last resorts haha

@sleepydvdr

Another potential solution, I will need to try to test the run time for this one since if I had a 10 MB text file, will this be speedy?

@Zedna

The problem is that I will be running many queries , so eventually, the data I want won't be in the begining of the file, the output would look like: if I wanted to get the second number, I will have to find it at the bottom of the text file

mysql> select date, sum(visitors) folks
    -> FROM clpr1_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
    -> GROUP BY date_fk;
+------------+-------+
| date   | folks |
+------------+-------+
| 2011-10-27 |  1462 |
mysql> select date, sum(visitors) folks
    -> FROM fgret_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
    -> GROUP BY date_fk;
+------------+-------+
| date   | folks |
+------------+-------+
| 2011-10-27 | 5364 |

@BrewManNH

I definitely need to clean up the SQL for sure, but even then, If I ran it for 50-100 different "things" my output at best will look like (putty's logging function will record everything from the command line), which means I will still have to "find" the number I'm looking for

mysql> select date, sum(visitors) folks
    -> FROM fgret_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
    -> GROUP BY date_fk;
+------------+-------+
| date   | folks |
+------------+-------+
| 2011-10-27 |  1462 |
mysql> select date, sum(visitors) folks
    -> FROM clpr1_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
    -> GROUP BY date_fk;
+------------+-------+
| date   | folks |
+------------+-------+
| 2011-10-27 | 5364 |
.
.
.

Edited November 9, 2011 by huskies

[kylomas]

huskies,

I completely missed the fact that you are creating this from an SQL stmt. Given that, does zednas solution not work??

kylomas

P.S. your last reply to zedna is somewhat ambiguos.

Edited November 9, 2011 by kylomas

[BrewManNH]

Just wondering, is there any way for you to interact with the SQL data base without using Putty? That would make things even easier.

[huskies]

@kylomas

zedna solution won't work because I will be running the SQL quries many many times, and each time I will want to extract one of the numbers. So his solution may work the second time, but becasue putty records the output in a text file in a top down fashion (so new results are appended at the end of the text file) the second time that I run the SQL query, the number that I want will not longer show up at the top of the file, it will now be at the bottom of the text file

@BrewManNH

What kind of ways are you thinking of? I'd love to hear some ideas. In the grand scheme of things, I trying to have Autoit queries up to 100-200 things/day, and get me those 100-200 numbers that I am interested in (each query would return a table, and I want to know the number after the date, for a specific date) and compare them to a set of another 100-200 numbers (which I will get by running another linux command) and alert me if there is a huge difference. So far I've got a proof of concept to work, but it will only read the very last line of a file.

Edited November 9, 2011 by huskies

[UEZ]

Try this:

#include <FileConstants.au3>
$logfile = @WindowsDir& "WindowsUpdate.log"
$line = 5
MsgBox(0, "Test", "Get content from last " & $line & " line:" & @LF & @LF & GetLastLine($logfile, $line))

Func GetLastLine($sFilename, $line = 5, $offset_bytes = 1024) ;coded by UEZ
    If $line < 1 Then $line = 1
    Local $size = FileGetSize($logfile)
    If $size < $offset_bytes Then $offset_bytes = $size
    Local $hFile = FileOpen($logfile)
    If @error Then Return SetError(1, 0, 0)
    FileSetPos($hFile, -$offset_bytes, $FILE_END)
    Local $aResult = StringSplit(FileRead($hFile), @LF, 2)
    If @error Then Return SetError(2, 0, 0)
    FileClose($hFile)
    If UBound($aResult) - $line > 0 Then Return $aResult[UBound($aResult) - $line]
    Return SetError(3, 0, 0)
EndFunc

Br,

UEZ

Edited November 9, 2011 by UEZ

[Zedna]

@Zedna

The problem is that I will be running many queries , so eventually, the data I want won't be in the begining of the file, the output would look like: if I wanted to get the second number, I will have to find it at the bottom of the text file

What's problem to do 50 queries in loop:

- run SQL

- read TXT output

- delete TXT

Edited November 9, 2011 by Zedna

[huskies]

I don't think putty allows you to delete their log files while a session is open / so the only way this would work is if I did 1 query per session, that way I will get a fresh log file everytime. However I want to avoid doing that since that will really make the entire script that much slower.

[jchd]

If I understand you problem well, you run similar queries against various tables and need to find the last availabe result for a given table.

I assume that you use a SELECT guaranteed to return only one row, or order the group by in the required order to get the interesting row first, and limit the output to one row using LIMIT 1.

Then a simple regexp will fetch the last available result for table fgret_f_unique_visitors (your example) if you take care to label the line with a fixed string that will be then very easy to find. If you have various queries (returning the same output structure) invent a unique name for them.

mysql> select 'FGRET_F_QueryName' as "Query", date, sum(visitors) folks -> FROM fgret_f_unique_visitors LEFT JOIN dd_date ON date_fk = id   -> GROUP BY date_fk;+------------+-------+| Query | date   | folks |+------------+-------+| FGRET_F_QueryName | 2011-10-27 |  1462 |mysql> select 'CLPR1_QueryName' as "Query", date, sum(visitors) folks   -> FROM clpr1_f_unique_visitors LEFT JOIN dd_date ON date_fk = id   -> GROUP BY date_fk;+------------+-------+| Query | date   | folks |+------------+-------+| CLPR1_QueryName | 2011-10-27 | 5364 |

The regex you then need will look for the last occurence of FGRET_F_QueryName, or CLPR1_QueryName, ...

The _QueryName part is a place holder to let you give unique labels to queries fetching data from the same table.

Example off the top of my head:

CLPR1_total for total visitors

CLPR1_invited for invited visitors

CLPR1_paying for paying visitors

FGRET_F_paying for paying visitors

Obviously you need to parametrize the regexp to include the result label, but this might be easy to do.

EDIT:

I just tried a simplified setup with 20Mb input file full of duplication of your (last) example. The input is a file with 68161 lines typical of your output. Here's what I get on a very slow laptop:

; look, Ma' no error checking!
Local Const $queryLabel = "FGRET_F_QueryName"
Local $inp = FileRead("testputty.txt")
Local $res = StringRegExp($inp, "(?ims)(?:\|\s+" & $queryLabel & "\s+\|\s+\d\d\d\d-\d\d-\d\d\s+\|\s+)(\d+)(?:\s+\|)(?!.*" & $queryLabel & ")", 1)
ConsoleWrite("Result of search for " & $queryLabel & " returned value " & $res[0] & @LF)

Result of search for FGRET_F_QueryName returned value 68153
+>01:56:11 AutoIT3.exe ended.rc:0
>Exit code: 0    Time: 3.350

File extract:

mysql> select 'FGRET_F_QueryName' as "Query", date, sum(visitors) folks
        -> FROM fgret_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
        -> GROUP BY date_fk;
+------------+-------+
| Query | date   | folks |
+------------+-------+
| FGRET_F_QueryName | 2011-10-27 |  1462 |
mysql> select 'CLPR1_QueryName' as "Query", date, sum(visitors) folks
        -> FROM clpr1_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
        -> GROUP BY date_fk;
+------------+-------+
| Query | date   | folks |
+------------+-------+
| CLPR1_QueryName | 2011-10-27 | 5364 |
...
<around 68 000 lines later>
...
mysql> select 'FGRET_F_QueryName' as "Query", date, sum(visitors) folks
        -> FROM fgret_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
        -> GROUP BY date_fk;
+------------+-------+
| Query | date   | folks |
+------------+-------+
| FGRET_F_QueryName | 2011-10-27 |  68153 |
mysql> select 'CLPR1_QueryName' as "Query", date, sum(visitors) folks
        -> FROM clpr1_f_unique_visitors LEFT JOIN dd_date ON date_fk = id
        -> GROUP BY date_fk;
+------------+-------+
| Query | date   | folks |
+------------+-------+
| CLPR1_QueryName | 2011-10-27 | 68160 |

Edited November 10, 2011 by jchd