Sign in to follow this  
Followers 0
huskies

Readfile in reverse (Read the 5th last line)

18 posts in this topic

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 :D

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

<It Shall Be Done>

Share this post


Link to post
Share on other sites



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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

kylomas

Thanks for your reply. My question was not "why is my code not working for that 1% of the time" :D 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 :oops:


<It Shall Be Done>

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

Would something like this work for you?

#include <file.au3>

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

#include <ByteMe.au3>

Share this post


Link to post
Share on other sites

@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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by Zedna

Share this post


Link to post
Share on other sites

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?


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

@kylomas

I'm going to give a filesetpos try, it looks somewhat promising :D 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 by huskies

<It Shall Be Done>

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

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 by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

@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 by huskies

<It Shall Be Done>

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Share this post


Link to post
Share on other sites

#16 ·  Posted (edited)

@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 by Zedna

Share this post


Link to post
Share on other sites

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.


<It Shall Be Done>

Share this post


Link to post
Share on other sites

#18 ·  Posted (edited)

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0