ImportCumulusFile

Name:	ImportCumulusFile
Type:	PHP
Author:	David A Jamieson
Contact:	'DAJ' via the forum
Last update:	3 December 2012
Version:	1.4a

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

Installation

• Download the PHP script ... (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.php 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

• 1.4a - 3 December 2012, Mark Crossley. Fixes syntax errors in 1.4, adds parameter presence checking. [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. [Download]
• 1.3a - 30 Nov 2012, Mark Crossley, added line breaks to output text [Download]
• 1.3 - 27 Nov 2012, Mark Crossley, updated for Cumulus b1050 [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 [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