ImportCumulusFile

Name:	ImportCumulusFile
Type:	PHP
Author:	David A Jamieson
Contact:	'DAJ' via the forum
Last update:	4 Feb 2010
Version:	1.1

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 Cumulus Log already uploaded

Installation

• Download the PHP script ... http://wiki.sandaysoft.com/files/ImportCumulusFile.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 is a sixth entry which may require editing. Look at the text file you are importing. How is each field separated/delimited? In the UK this is a comma so there is nothing else needs changed. If for example your delimiter is a semi-colon then change the line $delimiter accordingly

• 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 or monthly

• 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.1 Added the facility to identify the delimiter used on the import file
• 1.0 First Release