ImportCumulusFile: Difference between revisions

From Cumulus Wiki
Jump to navigationJump to search
(→‎Version Control: Added download links)
(Updated to v1.3a)
Line 1: Line 1:
{{AddOnBanner|name=ImportCumulusFile|type=PHP|author=David A Jamieson|contact='DAJ' via the forum|updated=28 November 2012|version=1.3}}
{{AddOnBanner|name=ImportCumulusFile|type=PHP|author=David A Jamieson|contact='DAJ' via the forum|updated=30 November 2012|version=1.3a}}


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


=Installation=
=Installation=
*Download the PHP script ... http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3.txt (right click, Save as)
*Download the PHP script ... [http://wiki.sandaysoft.com/files/ImportCumulusFile_v1-3a.txt (right click, Save as)]
*Save the file and edit it using a text editor --  there are five entries to be edited
*Save the file and edit it using a text editor --  there are five entries to be edited
:servername, username, password, database and security key
:servername, username, password, database and security key
Line 65: Line 65:
= Version Control =
= Version Control =


*1.3a - 30 Nov 2012, Mark Crossley, added line breaks to output text [[http://wiki.sandaysoft.com/filesImportCumulusFile_v1-3a.txt Download]]
*1.3 - 27 Nov 2012, Mark Crossley, updated for Cumulus b1050 [[http://wiki.sandaysoft.com/filesImportCumulusFile_v1-3.txt Download]]
*1.3 - 27 Nov 2012, Mark Crossley, updated for Cumulus b1050 [[http://wiki.sandaysoft.com/filesImportCumulusFile_v1-3.txt Download]]
*1.2 - 6 May 2011, Mark Crossley, updated for Cumulus 1.9.1  
*1.2 - 6 May 2011, Mark Crossley, updated for Cumulus 1.9.1  

Revision as of 09:09, 30 November 2012

Name: ImportCumulusFile
Type: PHP
Author: David A Jamieson
Contact: 'DAJ' via the forum
Last update: 30 November 2012
Version: 1.3a

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