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

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

Code: Select all

$delimter=",";
should be changed to

Code: Select all

$delimter=";";
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 :bash:

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 ..)