FileReadLines (Large files)

[logo78]

@Malkey, i'll try it soon and will give a feedback. Thy u so much.

@jchd,

thank u for ur honest reply and for ur affecting offer. certainly! ur right, but u have to understand that this case is more specific (yeah, every case is specific ) and i was just afraid to annoy the people with useless (i thought) overhead. I didnt guess, that there be people out here, which could be interested in that details, otherwise its not the lazyness to write down the few sentences.

We construct the plants, our customer are running them. I hope to belong to the creative minded engineers (cleaner). therefore i got the order, to try to solve the problems there.

We really need only the data for just for backtracing some problems, otherwise they arent neccessary.

There are still a lot of bugs in our non-well documented plc programs. Its one of the few roads, to end this never-ending-story.

Unsurprisingly, the time is short and the money is gone. Thatswhy, i arranged me 4 old notebooks, which are installed in the corresponding switch cabinets. Why 4 Notebooks? Either i am sniffing

specific network ports (order telegrams as ascii). The customer was not able, to arrange one mirroring on a central managed switch, where the communication is getting together, therefore i am running my own switches (ports are mirrored) in each cabinet, to snif the telegram traffic. Otherwise, it would did one notebook as well.

Actually, the sources are more. Either, i have to log the communication interfaces with the other external plants, to exclude communication problems. But these data, are negligible less.

To keep track of the large data, i separated one trolley and each communication interface to one external CSV.

Conclusion / please look at the attachment.

To bypass the 2Gbyte and 4000 connection limit, to be more flexible i would switch to an SQL solution. I am able to create tables and log/write into them, even with our OPC client. But i have no idea, how to handle the created databases?! How to compress, how to extract, which client? ...

Even now, i am not sure and confused, if the osbtacle is worth to go on with a SQL solution.

[MvGulik]

whatever Edited February 7, 2011 by MvGulik

[MvGulik]

whatever Edited February 7, 2011 by MvGulik

[JoeCool]

noob suggestion here

reading a section or sometime completly the file in an array can help if u do many search in it ...

Edited March 21, 2010 by JoeCool

[jchd]

@logo78,

Don't take offense, I didn't mean you were lazy. But it's always more to the point to have a clearer picture of the context where questions arise, since that can make a huge difference in what can constitutes a practical, effective solution.

Thank you for your detailed chart that synthetizes much. BTW which tool did you use to produce it?

Now a couple more questions: why do you have 4000+ connections? Where are the log files hosted: on the 4 notebooks or copied to your office? How many people will write to and query the log files? It sounds like you currently have the CSV files stored on the notebooks and are querying them from your own office, right?

I understand your "money is gone" stance, even if that's likely to make me mad. OK, let's forget expensive solutions.

If there are several users using (read/modify/write) remote base(s), then I wouldn't recommend SQLite. SQLite is process and threadsafe, but is an embedded layer without client-server architecture. It entirely relies on filesystem locking features. Unfortunately, experience proves that most NFS implementations are buggy and unreliable for such intensive application (even in relatively light load conditions, there are a large number of "acquire lock/change lock type/release lock" operations). There are add-on open source client-server layers available, but AFAIK there is no AutoIt wrappers yet. I plan to make one but don't hold your breath.

You can still use a proven solution like PostgresQL which is free of charge and does come with a liberal BSD license (essentially "do what you want even if in commercial context"). PostgresQL 8.0+ works very well on Windows but needs more administration than SQLite and, even if it has more advanced features and peephole optimizations, is doesn't come with transactions right out of the box (a derivative, InnoDB, offers transactions, but the space requirements for data grow up very fast, which is unlikely to fit in your framework). That means that with standard PostgresQL, your operations are not A.C.I.D. contrary to the simpler SQLite, which offers isolated transactions and some capability of nested transactions right out of the box.

I still believe that, if I read you well, you can still go with the ready-to-use lightweight SQLite stuff. Let me dream and correct me where I get wrong.

If we assume that you (or say the person in charge of tracking problem-related data) only "rarely" need to get at the accumulated data and that in case of such investigation response time isn't a problem (i.e. you can wait say 10 seconds before a result gets available), then it's possible to have one DB per plant per hour (or for few hours, period could be determined later) prepared by each notebook and sent 7ziped to your office. When you make a query, your AutoIt program unzips the required file(s) -- several if an incident has to be tracked for hours/days ??-- and once the data is uncompressed, make the relevant query. That could work very well if I'm not misinterpreting your constraints.

The easy part with SQLite is that you create a DB (memory- or disk-based) in a fraction of second, ready to use. The DB itself is a simple file, portable to _any_ system: your cellphone for instance (most of them run SQLite). You can work with hundreds of local DBs without penalty and in your case, tuning the cache size should be very easy (allocate as much as possible!). OTOH, SQLite doesn't offer any user access right and relies on those of the filesystem and account your program is running under. So no GRANT/REVOKE possible. This set apart, I don't see fundamental blocks in this approach.

Tell me what you think. We could hopefully present you a skeleon working solution if the concept goes by by your actual needs.

[logo78]

Thx u very much, for ur competent explanations, which bestowed me some more research with google

Unfortunately, thanks to restrictive interfaces of siemens, i forgot to highlight especially, that i am restricted to a few oppurtinities. Sorry for the late information.

See Attachment please. Rather, i am limited to MS SQL (2005 server, u'll get for free) or to take the MySQL server (even then, i have to use an old ODBC driver, otherwise its not working).

The big question which i have: What i am going to do with Logs on the SQL Server, more specifically: how can i get my appointed rows from the SQL Server. Ideally with AutoIT

Connections = Each CSV data field which the MS Jet OLE Driver has to open (in my case, each column actually) (e.g. 21unit x 240Datafields = more than 4000).

I recognized, that after more +/- 4000 live connections, the OLE Driver is not able to open more. u even get not any error messages. Some google research affirmed this doubt.

The log files are hosted on the corresponding notebook. There is no user management (GRANT/REVOKE) for files/database neccessary.

Recently, if i have something to research, i have to connect per VPN&RAS (or i am on site),

stop the OPC-Client-Service (the logger), move manually the large CSV in another directory, copy my empty CSV's to the target directory,

start the OPC-Service again, casually compress the files and download them for research purpose on my own notebook.

btw: to the money; always the same old story

btw2: for my todo-lists, to making gant views, mind maps and scheduling. I use an invincible combo (all free, respective under GPL).

ToDoList(+Calendar Plugin), GanttViewer, FreeMind (needs Java), therefore PortableJava.

Each one are among themselve compatible and are able to export/import the other's files.

In a couple of few minutes, u can create ur mindmap, import as a todo-list (with scheduling, responisibilities, etc..) and view with the ganntviewer.

Or u create a todolist, to make it more clearly (for technical noobs) u can make a mindmap with freemind.

ToDo-list has Multiuser capability, so u can use it in ur company (my collegues, are forced from me, to use it )

If u want, i can upload my preconfigured and portable combo. (One Directory, 7zipped ~ 40Mbyte)

@MvGulik,

did u wrote the data generator just for this case? If yes, thx for effort and the major honour

Edited March 22, 2010 by logo78

[jchd]

Clearer again.

There should be no problem for you connecting via VPN to the notebooks and accessing the remote DB (MS SQL or MySQL). Search here and you'll find UDFs wrappers for both engines. Using a DB manager can even keep you from having to write any program, as you can issue your SQL queries using templates (I presume, mostly "select * from T where time between 'start date-time' and 'stop date-time'"). Since the DB schema is so simple and the queries use only basic functions, you can try your hand with SQLite before switching (the SQL will stay the same).

I don't know enough about MS or MySQL to advise, but there must exist the possibility to automatically purge old records (> 1 month) and perform any routine tasks (might require a small script running for that).

[junkew]

As I understand the following

• Your nice summary Summary
• Almost for each opc no new data received every 0.5 seconds
• As such most lines are "equal"
• Each Plant has about 20 Trolleys, for each of them i am have a specific CSV file
• You only use the logfiles when you are having to investigate an issue
• The reason is for troubleshooting (back tracing / reverse engineering). If there was a problem, i am picking up all log files, extracting the designated time (+/- 300secs),

  Nice to have but you seem to prefer to do this manually

• stop the OPC-Client-Service (the logger), move manually the large CSV in another directory, copy my empty CSV's to the target directory,
• start the OPC-Service again, casually compress the files and download them for research purpose on my own notebook.

Then I would

a.rewrite the log files to something differently

b.replace the space and the apostroph to nothing (saves about 50% in size)

c.split the line and the line following it in separate attributes and if they are equal just write out ;; (in between empty means should be read to be the same as line before) saves again 50% (so now we are at 20-25% of original size)

d. split them in a naming convention by the day

Something like <plantId><TrolleyId><yyyymmdd>.csv

e. Whenever needed I can search in those text files with grep or perl easily which will give me the linenumber

GREP -C 50 "dd.mm.yyyy" log*.csv

Search for grepwin for windows alternative to grep

f. Files could still easily be imported into databasetooling whenever needed

Then some calculation gives

Logsize per day = (24 * 60 * 60 * 2 * 1320) / 1024 / 1024 = about 217 megaByte a day. = about 40 megabyte a day if you apply above rules to the CSV

The coding solution in AutoIt could become something like

expandcollapse popup

; Open file
$hFileIn = FileOpen("testfile.csv", 0)

; Read in lines of text until the EOF is reached
$logFileName=""
$arrFields1=""
$lineNew="" 
$prevDate=""
While 1
    
    $line1 = FileReadLine($hFileIn)
    If @error = -1 Then 
        ExitLoop
    EndIf
    $c1=stringlen($line1)       ;   For determining the compression rate
    $line1=stringreplace($line1," ","")
    $line1=stringreplace($line1,"'","")

;~  'If its the first line just copy it over
    if $arrFields1="" Then
        $arrFields1=stringsplit($line1,";")
        $prevDate=stringleft($arrfields1[1],10)
        $lineNew=$line1
        $logFileName="log" & $prevDate & ".csv"
        $hFileOut = FileOpen($logFileName, 1)
    Else
        $arrFields2=stringsplit($line1,";")
        ;~  'Check if new log has to be created
        if stringleft($arrfields2[1],10) <> $prevDate Then
            fileclose($hFileOut)        ;Close the currently open file  
            $arrFields1=stringsplit($line1,";")
            $prevDate=stringleft($arrfields1[1],10)
            $lineNew=$line1
            $logFileName="log" & $prevDate & ".csv"
            $hFileOut = FileOpen($logFileName, 1)
        else    
;~      'Do the copy of each field only if its not equal    
            $lineNew=$lineNew & $arrfields2[1] ; Allways copy the date/time field
            for $i=2 to $arrFields1[0]
                if $arrfields1[$i]=$arrfields2[$i] Then
                    $lineNew=$lineNew & ";" 
                Else
                    $lineNew=$lineNew & $arrfields2[$i] & ";" 
                endif   
                $arrFields1[$i]=$arrFields2[$i]
            next
        endif
    endIf
    
    $c2=stringlen($line1)
    $c3=stringlen($lineNew)
;~  consolewrite("a:" & $arrFields1[0] & ":" & $c1 & ":" & $c2 & ":" & $c3 & ":" & $line1 & @CRLF)   ; Show original line
;~  consolewrite("b:" & $arrFields1[0] & ":" & $c1 & ":" & $c2 & ":" & $c3 & ":")
    FileWriteLine($hfileOut, $lineNew)
;~  consolewrite($logFileName & $lineNew & @CRLF) ; Show new line
    $lineNew="" 
    
Wend

; Close file
FileClose($hFileIn)
FileClose($hFileOut)

If you start with the csv beeing like this

'17.03.2010 16:03:59'; '1'; '5'; '7'; '128'; '0'; '207900'; '526'; '0'; '0'; '0'; '48'; '2'; '2'; '2'; '5'; '0'; '0'; '17'; ; '225'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '536'; '0'; '536'; '0'; '536'; '0'; '27'; '27'; '64'; '0'; '16'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '255'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '7'; '0'; '1'; '0'; '7'; '0'; '1'; '0'; '0'; '0'; '1'; ; ; '507'; '508'; '509'; '510'; '511'; '512'; '513'; '514'; '515'; '516'; '517'; '518'; '519'; '520'; '521'; '522'; '523'; '524'; '525'; '526'; '527'; '528'; '529'; '530'; '531'; '532'; '533'; '534'; '535'; '536'; '537'; '0'; '0'; '0'; ; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '8'; '0'; '0'; '0'; '0'; '0'; '128'; '506'; '536'; '536'; '0'; '0'; '0'; '37'; '2'; '1'; '1'; '1'; '0'; '2'; '1'; '1'; '521'; '0'; '0'; '0'; '410'; '1'; '506'; '256'; ; '98'; '0'; '410'; '512'; '506'; '512'; '98'
'17.03.2010 16:03:59'; '1'; '5'; '7'; '128'; '0'; '208987'; '526'; '0'; '0'; '0'; '48'; '2'; '2'; '2'; '5'; '0'; '0'; '17'; ; '225'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '536'; '0'; '536'; '0'; '536'; '0'; '27'; '27'; '65'; '0'; '16'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '255'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '7'; '0'; '1'; '0'; '7'; '0'; '1'; '0'; '0'; '0'; '1'; ; ; '507'; '508'; '509'; '510'; '511'; '512'; '513'; '514'; '515'; '516'; '517'; '518'; '519'; '520'; '521'; '522'; '523'; '524'; '525'; '526'; '527'; '528'; '529'; '530'; '531'; '532'; '533'; '534'; '535'; '536'; '537'; '0'; '0'; '0'; ; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '8'; '0'; '0'; '0'; '0'; '0'; '128'; '506'; '536'; '536'; '0'; '0'; '0'; '37'; '2'; '1'; '1'; '1'; '0'; '2'; '1'; '1'; '521'; '0'; '0'; '0'; '410'; '1'; '506'; '256'; ; '98'; '0'; '410'; '512'; '506'; '512'; '98'
'17.03.2010 16:04:00'; '1'; '5'; '6'; '128'; '0'; '210541'; '527'; '0'; '0'; '0'; '48'; '2'; '2'; '2'; '5'; '0'; '0'; '17'; ; '225'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '536'; '0'; '536'; '0'; '536'; '0'; '27'; '27'; '66'; '1'; '16'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '255'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '7'; '0'; '1'; '0'; '7'; '0'; '1'; '0'; '0'; '0'; '1'; ; ; '507'; '508'; '509'; '510'; '511'; '512'; '513'; '514'; '515'; '516'; '517'; '518'; '519'; '520'; '521'; '522'; '523'; '524'; '525'; '526'; '527'; '528'; '529'; '530'; '531'; '532'; '533'; '534'; '535'; '536'; '537'; '0'; '0'; '0'; ; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '8'; '0'; '0'; '0'; '0'; '0'; '128'; '506'; '536'; '536'; '0'; '0'; '0'; '37'; '2'; '1'; '1'; '1'; '0'; '2'; '1'; '1'; '521'; '0'; '0'; '0'; '410'; '1'; '506'; '256'; ; '98'; '0'; '410'; '512'; '506'; '512'; '98'
'17.03.2010 16:04:01'; '1'; '5'; '6'; '128'; '0'; '212080'; '527'; '0'; '0'; '0'; '48'; '2'; '2'; '2'; '5'; '0'; '0'; '17'; ; '225'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '536'; '0'; '536'; '0'; '536'; '0'; '27'; '27'; '66'; '0'; '16'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '1'; '0'; '0'; '0'; '0'; '0'; '255'; '1'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '7'; '0'; '1'; '0'; '7'; '0'; '1'; '0'; '0'; '0'; '1'; ; ; '507'; '508'; '509'; '510'; '511'; '512'; '513'; '514'; '515'; '516'; '517'; '518'; '519'; '520'; '521'; '522'; '523'; '524'; '525'; '526'; '527'; '528'; '529'; '530'; '531'; '532'; '533'; '534'; '535'; '536'; '537'; '0'; '0'; '0'; ; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '0'; '8'; '0'; '0'; '0'; '0'; '0'; '128'; '506'; '536'; '536'; '0'; '0'; '0'; '37'; '2'; '1'; '1'; '1'; '0'; '2'; '1'; '1'; '521'; '0'; '0'; '0'; '410'; '1'; '506'; '256'; ; '98'; '0'; '410'; '512'; '506'; '512'; '98'

It would then become basically this

17.03.201016:03:59;1;5;7;128;0;207900;526;0;0;0;48;2;2;2;5;0;0;17;;225;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;536;0;536;0;536;0;27;27;64;0;16;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;255;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;7;0;1;0;7;0;1;0;0;0;1;;;507;508;509;510;511;512;513;514;515;516;517;518;519;520;521;522;523;524;525;526;527;528;529;530;531;532;533;534;535;536;537;0;0;0;;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;8;0;0;0;0;0;128;506;536;536;0;0;0;37;2;1;1;1;0;2;1;1;521;0;0;0;410;1;506;256;;98;0;410;512;506;512;98
17.03.201016:03:59;;;;;208987;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;65;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
17.03.201016:04:00;;6;;;210541;527;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;66;1;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
17.03.201016:04:01;;;;;212080;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;0;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[MvGulik]

whatever Edited February 7, 2011 by MvGulik

[jchd]

Here is a toy example of how it can be made to work using a database. I use a local SQlite base but things could be similar using another engine.

BTW does the logging program grab exclusive write access to the logging file permanently or does it open-append-close for each write?

Logs.zip

[logo78]

There should be no problem for you connecting via VPN to the notebooks and accessing the remote DB (MS SQL or MySQL). Search here and you'll find UDFs wrappers for both engines.

As well this is the biggest advantage i see!

If i have something to investigate, i just connect to the DB, and query the lines, which i need.

The recent solution, requires that i have to connect per RAS, compress the the CSV files, download them, decompress on my local PC, extract the lines, etc... Much more steps and takes much more time.

The worst scenario for handling the growing DB, respective purging old records, if there is nowhere a solution,

either i can stop the OPC-client and the MySQL service, and compress it (triggered with Size-limit or better with the time (daily/weekly) with MS or AutoIT).

Thanks for ur attachment (wow!), i already took it and obviously will give it a try. Today i am driving on site and hopefully i will have some time, to try a DB solution (at least on 1 plant).

Ur efforts will be rewarded soon at least with some pictures and videos from the amazing plant; exclusive for ya

@junkew, thank u for ur constructive idea. Unfortunately the mentioned point of @MvGulik is absolutely right. As well, i could advise my OPC Client to just record changed data or to trigger with a specific value (e.g. record only if trigger: $i_have_a_order=1), but there are so much actors which could be the trigger, so much action, movements on different axis and so much issue-interfaces, that it would be a mess, if i have to search (dont forget murphy) on every case the corresponding values which belongs to each other. btw: with compression (solid) i get a rate about 1/500-1/1000

Edited March 23, 2010 by logo78

[IchBistTod]

I have skimmed through this. And I would like to propose a solution to you.

First you could read through the file line by line by making an array via string split.

$file = FileRead("FILE HERE")
    $lines = StringSplit(StringReplace($file, @cr, ""), @lf)

$data[0] then contains number of lines, to parse each line simply use a for loop

for $i=1 to $lines[0]
;processing code here
next

once you are able to do this, you could use sqlite.

By using string split on each line with ";"

like so

for $i=1 to $lines[0]
$internal = stringsplit($lines[$i], ";")
next

then you could insert it into a database file.

using query like so

for $i=1 to $lines[0]
$internal = stringsplit($lines[$i], ";")
_SQLite_Exec (-1, "INSERT INTO DB1(datatitle1,datatitle2,datatitle3,datatitle4) VALUES ("$internal[1]&","&$internal[2]&","&$internal[3]&","&$internal[4]);")
next

using variable directly would be great as data is already in single quotation marks.

Then as suggested before you could use sqlite to sorth through the data/view it.

You could use a listview to list it out, and simple input box to execute search queries via key words in specific sets of data.

Hope this helps.

BTW you could put this dbfile + the app on a network share accessible via VPN to make it accessible to the whole network.

To keep the db up to date you could constantly have a converter script running in the background, to convert the format, and add into database and delete the old data.

Because the way databases are updated the database would always be up to date to all viewing it.

I have made an inventory app using sqlite, so I can say it is VERY useful.

Edited March 23, 2010 by IchBistTod

[jchd]

@IchBistTod

One of the issue is that accumulating CSVs are getting large quickly, > 2Gb. OTOH, it's roughly what my toy example does.

@logo78

Remaining question is: can you intercept log data without having to stop/start the OPC client and avoid file growth? Maybe a dumb idea, but couldn't you redirect OPC output to stdout and have an AutoIt script grab it on the fly? This way you have no problem with file growth and can select the storage strategy/engine which suits you best. If the OPC is using a hardcoded filename it may still be possible to create a symlink of that name pointing to a pseudo-device or something along this line and have AutoIt read there line by line. I don't know what is actually possible, I'm on thin ice here.

[MvGulik]

whatever Edited February 7, 2011 by MvGulik

[logo78]

@IchbisTod,

thanks for ur proposed solution. We are able to log into MySQL databases. What we have to aspire is to query the apointed Rows from the MySQL Database. In addition, what we'll do with the growing up database? How to purge/compress older data, ideally without to interrupt the OPC logging process. (For more clearness, may be i should the update/edit my 1st posting on this tread).

@jchd,

until now, unfortunately i had no time, to test ur example. To the remaining question: i think there is no other oppurtinity to compress/purge/backup the DB, without to stop the service, interrupt the logging itself. The biggest disadvantage here is, probably there are no oppurtinites to run the process/service with commandline parameters. So u have to do it with autoit (controlclick). Thats a little bit unreliable, but i have to test it. Although is evil, that u not logging for a this time anything.

The CSV's for example, are locked through the OPC service. Even ur not able to view the files until u stop the process.

The logging through the Database is made by the ODBC driver. I have no idea, how to simulate a virtual database, which could be handled per Autoit (switching at purging/compressing).

I am sure, that would be a possibility with hard/softlinks, if i would choose to go on with CSV's.

Until there is no solution, it seems, that i have to stop the processes (OPC Client & MySQL), make my thing (compress,purge) and start everything again.

MVGulik,

ur right. i now the side effect of solid archives. but the compressing will be automated through autoit (e.g. in the midnight) and it makes no difference, if it takes a few hours. Much more important is the transfer time through VPN (its not very fast). U have big size difference, especially with similar files with solid archives.

[jchd]

If logging to MySQL works, then there isn't much problem with DB growth and you don't have to interrupt the OPC beast anymore. Define a small table to store the number of rows in the DB (if MySQL doesn't have that function built-in) and update it in an insert trigger, then in the same trigger, you count the # rows older than the date just inserted + 1 full month (or the retention period you feel right), decrement #rows accordingly and delete them. This way, you're guaranteed to have an average upper bound for the size of the (active part of the) DB.

For DB vacuum, I don't know enough MySQL specifics to give you practical advice. I would tend to create a delete trigger on each statement (if that exists) and count the #rows deleted. After reaching a reasonable fixed percentage of the estimated DB size (say 10 or 20%) launch a vacuum operation. I just hope this can be done outside the write transaction. If not, then TADA! AutoIt to the rescue and connect once per day locally to do a vacuum without asking question.

The cool thing with DBs is that all this can be made to work concurrently without silly interaction, which isn't the case with discrete files.

I'm sure that there is enough MySQL knowledge around here or on a MySQL forum/list to crack this nut (without _you_ driving nuts).

Edited March 23, 2010 by jchd

[junkew]

I am not sure if a database solution will give you what you need as you then also have to implement archiving features, cleaning and probably will occupy a lot of diskspace etc.

How are you going to handle the massive amounts of data in a database? Not so easy on compression I think?

I am not sure under which circumstances you need the logfiles but I understood you would like to search on a date + time plus or minus about 5 minutes.

Handling the csv files is probably much easier especially if you can split them on the month (limits the amounts of CSV significantly) instead of on the day (see below)

As its OPC I assume you can use a COM/ACTIVEX object to access the OPC directly and you do not need to stop/start service copy files etc. if you deal with the interfaces directly

• http://en.wikipedia.org/wiki/OPC_Data_Access
• http://www.opcfoundation.org/
• http://www.matrikonopc.com/downloads/147/specifications/index.aspx
• http://www.opcda.com/products/opc-desktop-tools/index.aspx
• Sample visual basic http://www.docstoc.com/docs/20367676/Visual-Basic-and-OPC (plenty of people know how to convert to AutoIt)
• even better sample http://www.kepware.com/Support_Center/SupportDocuments/KTAN90039_Logging_Data_In_VB6.pdf
• http://www.opcconnect.com/freecli.php

Anyway I updated the CSV compression to be 40 times (1 gigabyte to about 25 megabyte) smaller than the original file while still beeing able to search on the timestamp (thats not possible with solid archiving) making it possible to search either with GREP tooling or AutoIT just do a stringinstr search or regexp search.

Viewer to make the original CSV lines visible is not that hard to make and also the search can be easy as 20-25 megabyte file should be easy to read in completely in AutoIt in one statement

I did now do a split on the date but just as easily you can split when a certain size is met or make weekly/monthly CSV files.

Read file at once like code was given before in thread

$file = FileRead("FILE HERE")
;~you could do a search now in the string $file
    $lines = StringSplit(StringReplace($file, @cr, ""), @lf) ;~split it back in lines array

compressed and split csv

expandcollapse popup

; Open file
$hFileIn = FileOpen("testfile.csv", 0)

; Read in lines of text until the EOF is reached
$logFileName=""
$arrFields1=""
$tline="" 
$prevSplitFile=""
While 1
    
    $line1 = FileReadLine($hFileIn)
    If @error = -1 Then 
        ExitLoop
    EndIf
    $c1=stringlen($line1)       ;   For determining the compression rate
    $line1=stringreplace($line1," ","")
    $line1=stringreplace($line1,"'","")

;~  'If its the first line just copy it over
    if $arrFields1="" Then
        $arrFields1=stringsplit($line1,";")
        $prevSplitFile=stringmid($arrfields1[1],4,2)  ;~ Split on the month
;~      $prevSplitFile=stringleft($arrfields1[1],10)  ;~ Split on the day
        $tLine=$line1
        $logFileName="log" & $prevSplitFile & ".csv"
        $hFileOut = FileOpen($logFileName, 1)
    Else
        $arrFields2=stringsplit($line1,";")
        ;~  'Check if new log has to be created
        if stringmid($arrfields2[1],4,2) <> $prevSplitFile Then
;~      if stringleft($arrfields2[1],10) <> $prevSplitFile Then
            fileclose($hFileOut)        ;Close the currently open file  
            $arrFields1=stringsplit($line1,";")
            $prevSplitFile=stringmid($arrfields1[1],4,2) ;~ Split on the month
;~          $prevSplitFile=stringleft($arrfields1[1],10) ;~ Split on the day 
            $tLine=$line1
            $logFileName="log" & $prevSplitFile & ".csv"
            $hFileOut = FileOpen($logFileName, 1)
        else    
;~      'Do the copy of each field only if its not equal    
            $tLine=$tLine & $arrfields2[1] ; Allways copy the date/time field
            for $i=2 to $arrFields1[0]
                if $arrfields1[$i]=$arrfields2[$i] Then
                    $tLine=$tLine & ";" 
                Else
                    $tLine=$tLine & $arrfields2[$i] & ";" 
                endif   
                $arrFields1[$i]=$arrFields2[$i]
            next
        endif
    endIf
    
;~  'Replace the ; with a ;(count) to reach a size of about 2-3% of original size while still beeing able to search with normal text tooling
    $tCount=0
    $lineNew=""
    for $i=1 to stringlen($tLine)
        if stringmid($tLine,$i,1)=";" Then
            $tCount=$tCount+1
        Else
            if $tCount=1 then $lineNew=$lineNew & ";" 
            if $tCount=2 then $lineNew=$lineNew & ";;" 
            if $tCount > 2 Then $lineNew=$lineNew & "(" & $tCount & ")"  ;~ (n) means then replace with n times ;
            $lineNew=$lineNew & stringmid($tLine,$i,1)
            $tCount=0
        EndIf
    next
        
    $c2=stringlen($tLine)
    $c3=stringlen($lineNew)

;~  FileWriteLine($hfileOut, $lineNew)
;~  consolewrite("a:" & $arrFields1[0] & ":" & $c1 & ":" & $c2 & ":" & $c3 & ":" & $line1 & @CRLF)   ; Show original line
    consolewrite("b:" & $arrFields1[0] & ":" & $c1 & ":" & $c2 & ":" & $c3 & ":") ; Show new line
    consolewrite($logFileName & $lineNew & @CRLF) ; Show new line

    $tLine="" 
    
Wend

; Close file
FileClose($hFileIn)
FileClose($hFileOut)