View source for ImportCumulusFile

{{AddOnBanner|name=ImportCumulusFile|type=PHP|author=David A Jamieson|contact='DAJ' via the forum|updated=13 March 2013|version=2.0}}

This is a PHP script designed to run on your webserver and import Cumulus log files into a MySQL database on your server.

=Requirements=
The web server must have..
*PHP
*MySQL
*A My SQL database, username and password
*a Cumulus Log already uploaded

=IMPORTANT NOTE FOR VERSION 2.0=
Version 2.0 changes the SQL table structures from version 1.x. If you are currently using version 1.x and wish to upgrade, then you will have to DROP the existing tables and let the version 2 script recreate them. If this is unacceptable DO NOT UPGRADE, stay with version 1.x

The new table formats use a combined date/time field instead of separate, this makes constructing queries much simpler, it is worth making the change.

Of course if you do change, then any existing queries you are using will also have to be rewritten - take this into account. It may be possible to create VIEWs of the new tables that mimic the old table format, and for you to use those views with your existing queries, that is an exercise for you!

=Installation=
*Download the PHP script ... [http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-0.txt (right click, Save as)]
*Save the file and edit it using a text editor --  there are five entries to be edited
:servername, username, password, database and security key
:these are documented in the file at the top.

:There are three further variables to consider and may require editing.  Look at the text file you are importing.  How is each field separated/delimited?  How is the date delimited?  How is a decimal number shown?  In the UK there is nothing else needs changed.  If for example your delimiter is a semi-colon and a dash then change the lines $field_delimiter and $date_delimiter accordingly.
:if your decimal separator is a comma this also needs to be changed in the file using the $decimal_separator variable

*save the file as ImportCumulusFile.<b>php</b> or whatever name you prefer and upload it to your webserver.

=Using the script=
Currently you can import two file types from Cumulus -- the dayfile and the Monthly log files.


Firstly decide the table to be populated in your SQL database.  If the table does not exist the script will create it.  Typically you have one table for the dayfile, and one or more for the
Monthly Logs.  You could import every monthly log file into one large SQL table. 

If you re-import existing data the script will update the data in the table so you can run the import every day on the same file, dayfile, for example


You must pass several options with your URL...
(They can be in any order but the first one must start with ? other with &)

*type=xxxx
::this must be either the phrase ''dayfile'', ''monthly'' or ''realtime''

*file=xxxxx
::the location on your webserver, relative to this script location, of your Cumulus File
::example file=dayfile.txt  or file=../data/Jan10log.txt

*table=xxxx
::the table within SQL to import the data.  If it does not exist the script will create it

*key=xxxxx
::A security key, unique to you, to pass as part of the URL.  This stops others from running the script on your server if the do not know the key.  


An example URL...

:htp://www.myserver.com/ImportCumulusFile.php?type=dayfile&key=letmein&table=Dayfile&file=./data/Jan10log.txt

=The result=

If all goes well it will create or update the table with the relevant data and report back the word "Done".  If the script fails there are a number of outputs it could return to explain the problem.


=Further automation=

Using the above script, in combination with the [[Toolbox]] you could set an automated FTP rule to upload the dayfile.txt from Cumulus each day at 00:15 and then run an HTTP remote command (this script) to import it into the SQL database


= Version Control =
*2.0 - 13 March 2013, Mark Crossley [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v2-0.txt Download]]
**Change monthly and realtime tables to use combined date/time columns rather than separate
**Added optional retention time for realtime table. Records older than the specified age will be deleted. retainVal=NNN retainUnit=XXXX
**Added checking to allow running of the script from a command line as well as via http
**Added 'extra' columns to record wind directions as compass points where they are only provided as bearings:
 Dayfile:  Added the following columns:- HWindGBearSym, DomWindDirSym
 Monthly:  Added the following columns:- WindbearingSym, CurrWindBearingSym
*1.4a - 3 December 2012, Mark Crossley. Fixes syntax errors in 1.4, adds parameter presence checking. [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-4a.txt Download]]
*1.4 - 3 December 2012, Mark Crossley. Added ability to create and log data to a 'realtime' table.
*1.3b - 30 Nov 2012, Mark Crossley, changed so ALL missing values from the dayfile are entered into the table as NULL. If you have used the Cumulus editor, then they will be present in the file as blank fields. [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3b.txt Download]]
*1.3a - 30 Nov 2012, Mark Crossley, added line breaks to output text [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3a.txt Download]]
*1.3 - 27 Nov 2012, Mark Crossley, updated for Cumulus b1050 [[http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3.txt Download]]
*1.2 - 6 May 2011, Mark Crossley, updated for Cumulus 1.9.1 
*1.1c Small modification to code to handle date delimiter with a dot [[http://wiki.sandsoft.com/files/ImportCumulusFile_v1-1c.txt Download]]
*1.1b Added a variable to consider decimal separator 
*1.1a Added date delimiter 
*1.1 Added the facility to identify the delimiter used on the import file 
*1.0 First Release

[[Category:WebTools]]