Page 1 of 9
a PHP import script for DayFile and Monthly log files
Posted: Wed 03 Feb 2010 10:36 pm
by daj
I have written a PHP script to read the contents of the dayfile.txt or a Monthly log file and import these into a MySQL database on your webserver.
Handy if you want to get the Cumulus data onto the web to query it and write your own reports/graphs directly from SQL
Full details on the wiki
http://wiki.sandaysoft.com/a/ImportCumulusFile
As an example, I upload my dayfile.txt just after midnight each day and then process it using this script. The result is my Historic Data page ...
http://www.grantownweather.co.uk/historic/historic.php... which reader the data live from the SQL database and presents it in a table for viewing.
All my monthly log files are also in SQL so I am working on analysis tools for those too
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 10:32 am
by nitrx
Goodmorning David
I get errors at first I got an error could not conect to database I changed the databasehost from nummeric to 'localhost' but then I get the error 'failed to open file' , I'm sure its in the directory I've specified.
When I succed how do I present de data on a webpage ? I even dont understand the securuty key when the url to proceed the script is on the web.
Looking forward to use this future anyway
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 10:37 am
by nitrx
Oep fixed I forgot a trailing point . for the import url I used /dayfile.txt instead ./dayfile.txt so the data are imported
my other questions are still open
Edit
only one record is imported and the datafield is 0000-00-00 I think its again about the delimiters
LogDate HighWindGust HWindGBear THWindG MinTemp TMinTemp MaxTemp TMaxTemp MinPress TMinPress MaxPress TMaxPress MaxRainRate TMaxRR TotRainFall AvgTemp TotWindRun HighAvgWSpeed THAvgWSpeed
Wijzigen Verwijderen 0000-00-00 6 N 2;12: 4 2;19: 2 5;1 8 6;4 1 0 0 0
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 11:19 am
by daj
Ron
I will make a modification to the code to allow you to easily change the delimiter. You could edit the script yourself but let me make it a little easier....two minutes
On your other questions....
How to present the data...that is out with the scope of the script. All we are doing with the script is providing a mechanism to get the data into a SQL database. There are hundreds of tools that can extract data and present it on a website. For example, JpGraph can pull it's data from SQL; on my site I extract the data into a table to view onscreen;.... I will probably release my table page to the Community too
Not understanding the security key....Not sure what else I can say. We need to provide a way to stop anyone running your import script. So if I knew you were using the script I could probably guess the parameters and could potentially 'screw' up the data by importing the wrong thing. To overcome this I added a simple checking process. You add a phrase/word to the PHP script (the $key=" ") line. Then when YOU call the script you include that key....
www.myserver.com/ImportCimulusFile.php. ... ingspecial
If SOMEONE ELSE (say me) attempts to run the script I can not as I do not know you special key
So I already know almost enough to run the script myself....
Code: Select all
http://www.apeldoorn.tk/weer/ImportCumulusFile.php?file=./dayfile.txt&table=MyTable&type=dayfile
But I do not know your key (which you should change in the script!) so I can not run it
Hope that helps
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 11:30 am
by nitrx
Thanks for your explanation David, I'm a complete noob in php and sql programming so it would be great when you can add an import script for my delimeters.
About presenting on the web I only need some examples I'm not intendendig using the same layout but it has no rush.
Thanks in advance
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 11:41 am
by daj
Version 1.1 now released to include the option to change delimiter
Ron, you need to download the update and change all the settings for username, etc again. In addition
should be changed to
to accommodate your semi-colon
let me know how it goes. Make sure version 1.1 is downloaded and you browser does not try to pull a cached version down!
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 11:46 am
by nitrx
Almost good but there is only one record imported the date is 0000-00-00 other values look fine now for your infomation this is how my dayfile.txt is on the server
http://www.apeldoorn.tk/weer/dayfile.txt I'm going to lunch
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 11:56 am
by daj
I blame Steve!
The file formats are noted as being comma separated and the date is always in the format dd/mm/yy
You are semi-colon separated and your date is dash delimited.
let me tweak again, and add another delimiter for date
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 12:11 pm
by daj
Now updated to include a date delimiter too
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 12:21 pm
by steve
daj wrote:I blame Steve!
The file formats are noted as being comma separated and the date is always in the format dd/mm/yy
Comma-separated means different things in different countries, sadly. The files are still called "CSV" files, even though the "C" is not strictly a comma. I guess I should say they are "list-separator separated".
The date format is really just an indicator of the order of the numbers and the number of digits in each part. I guess I should say the format is "dd<some character>mm<some character>yy".
To be honest I had absolutely no idea when I wrote the code that when I told Delphi to use a comma to separate the items that it would be clever and use the system's list separator instead (just as well, really, given that the numbers themselves have commas in them; I didn't know that would happen either). I had no idea either that when I told Delphi to use "dd/mm/yy" as the date format that it would actually not use a slash but would use the system date separator instead.
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 12:28 pm
by daj
Thanks Steve, no worries -- I just needed to blame someone. LOL I might put a note on the Wiki page for the file formats to note that delimiters may change in your country.
It's a learning curve for me (and others too) so we can build that into our thinking in future scripts
The world would be so much easier if we all did the same thing, but probably much more boring
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 12:38 pm
by nitrx
Well it works great job (almost) all data are imported but the values don't have decimals ergo -10,5 (temperature) in my case is stored as -10
I think we can't blame Steve we should blame all the diffrent conventions of notations the Mile , inches and feet an Fahrenheits should be forbidden thats why some missiles crash when diffrent nations do the programming of the computers I think
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 12:48 pm
by daj
Anyone want to help here?
I have no idea why 10,5 would drop it's decimal in MySQL.
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 12:52 pm
by steve
I would guess that MySQL is expecting a decimal point rather than a comma?
Re: a PHP import script for DayFile and Monthly log files
Posted: Thu 04 Feb 2010 1:01 pm
by nitrx
daj wrote:Anyone want to help here?
I have no idea why 10,5 would drop it's decimal in MySQL.
It doesn't say it's decimal but float (really don't know what it is ..)